Help with a SQL Query using temp tables

Hi All,

I have 4 temporary tables that hold criteria selected through a report wizard.
I've created a SQL statement and used the four tables in my WHERE/ AND clauses but the results retuned are not being filtered correctly.

Would somebody be kind enough to help me out please.

To briefly summarise, I have created a SQL statement that returns all rows in my recordset, I now need to implement some additional SQL to filter the recordset using my temporary tables, which contain the filters as follows:

(1) Temp table 1 (##tblTempAssetFilt) is mandatory and will always contain at least one row.
(2) Temp table 2 (##tblTempRepairTypeFilter) is optional and may never contain any rows. If this is the case then I have no reason to filter my resultset against this table.
(3) Temp table 3 (##tblTempRepairFilter) / Temp table 4 (##tblTempRepairElementFilter) are both optional, only one of these tables will contain data at one time. Again, as an optional filter the tables may never contain rows, and thus need to be ignored.

I have the following SQL, can somebody tell me how I would go about filtering the recordset using the temporary tables. The creation of the temporary tables occurs at the beginning so will always exist even when no rows have been inserted.

SELECT *
FROM tblActualWork [ActualWork]
JOIN tblRepair [Repair] ON ActualWork.intRepairID = Repair.intRepairID
JOIN tblRepairElement [RepairElement] ON Repair.intRepairElementID = RepairElement.intRepairElementID
JOIN tblRepairType [RepairType] ON Repair.intRepairTypeID = RepairType.intRepairTypeID
JOIN tblAsset [Asset] ON ActualWork.intAssetID = Asset.intAssetID
WHERE ActualWork.intAssetID IN (Select intAssetID From ##tblTempAssetFilter) AND Repair.intRepairTypeID IN (Select intRepairTypeID From ##tblTempRepairTypeFilter)
AND Repair.intRepairID IN (Select intRepairID From ##tblTempRepairFilter)
AND Repair.intRepairElementID IN (Select intRepairElementID From ##tblTempRepairElementFilter)

Any filtering must be based on the recordset filtered by temp table 1, which is a mandatory filter. Rows will always exist in this temp table.

Please help, not having much joy with this. Many thanks.Basically all I did was move your manditory temp table out of the where clause and add ORs to the where so that if a table was empty you would evaluate to TRUE for that table. Maybe not the best solution but this should get you going.

SELECT *
FROM tblActualWork [ActualWork]
JOIN tblRepair [Repair] ON ActualWork.intRepairID = Repair.intRepairID
JOIN tblRepairElement [RepairElement] ON Repair.intRepairElementID = RepairElement.intRepairElementID
JOIN tblRepairType [RepairType] ON Repair.intRepairTypeID = RepairType.intRepairTypeID
JOIN tblAsset [Asset] ON ActualWork.intAssetID = Asset.intAssetID
join ##tblTempAssetFilter [TempAssetFilter] on ActualWork.intAssetID = TempAssetFilter.intAssetID
WHERE (Repair.intRepairTypeID IN (Select intRepairTypeID From ##tblTempRepairTypeFilter) or not exists(select * from ##tblTempRepairTypeFilter)
AND (Repair.intRepairID IN (Select intRepairID From ##tblTempRepairFilter) or not exists(select * from ##tblTempRepairFilter)
AND (Repair.intRepairElementID IN (Select intRepairElementID From ##tblTempRepairElementFilter) or not exists(select * from ##tblTempRepairElementFilter)|||Paul,

Thank you for your reply, this has indeed fixed my problem. I am now returning a recordset with the desired results using my temporary tables.

Thanks again.|||Cool!

Here is a twist, if you do not need global temp tables I would switch to using table variables, there are some restrictions to using table variables but they can be much faster.

Also, if you can pre test the emptiness of your tables and store the reslts in a bit variable you can speed things up even more. As it is you are testing for empty temp tables each time you move to a new row.

Help with a confusing and advanced query - If Exsists?

Ok,

I need to select a list of products based on a complex criteria. First off, here is a list of the tables I am using and their function.

Dbo.Products – This table holds product names and information.

Dbo.Products_Attributes – This table holds a list of attributes for each product. It has a many to 1 relationship with Products. This table holds only 2 fields – ProductID and AttributeID

Dbo.Customers – This table holds basic customer information and some of the needed criteria for the product search.

Dbo.Customers_Attributes – This table contains a list of attributes that the customer needs in his/her products. This table has a many to 1 relationship with Customers and only has 2 fields, CustomerNum and AttributeID

Dbo.Attributes – This table contains all of the different attributes possible for our products to have. Each can be used by a customer when choosing criteria.

Background: Each attribute is a True / False. Either they want that attribute, or they do not care if they get it or not. This is where the hard part comes in. If a particular attribute is listed as needed in dbo.Customers_Attributes, then I do not want any records pulled from Products that DOES NOT have this attribute. On the other hand, if the attribute is NO listed in the customers_attributes list, it is assumed that the customer wants it. In other words, if there were NO attributes in the customers_attributes list, ALL products would be returned. Attributes listed in the customers_attributes table are "required" (no product will be returned that does not have what is in the list).

I hope I was clear enough… I had to change the table names a functions slightly because of some stupid policy about giving out too much proprietary information here.

If any of you know how to do the above with a subquery of some kind, please let me know.

Dave LarsonI'm not sure that I catch your task properly, but try something like that:

SELECT ProductId, ProductName
FROM dbo.Products
WHERE (NOT (ProductId IN
(SELECT dbo.Products.ProductId
FROM dbo.Products INNER JOIN
dbo.Product_Attributes ON dbo.Products.ProductId = dbo.Product_Attributes.Product
WHERE (dbo.Product_Attributes.Attribute IN
(SELECT dbo.Castomer_Attributes.Attribute
FROM dbo.Castomer_Attributes INNER JOIN
dbo.Customers ON dbo.Castomer_Attributes.Customer = dbo.Customers.CustomerId
WHERE (dbo.Customers.CustomerId = @.CustomerId))))))

where @.CustomerId is a parameter.

help to define a search criteria with FTS

Hi!
I'm using FTS in MSSQL2000.
1. i have a string "bcd" and i want the results : "abcd" or "1bcd" but
not "bcda" or "aabcd" (always from the second letter).
2. the search column is a long string. the begining of the string is
more important that the end. i want the results found in the begining
to get higher RANK so i can present them first.
Please help.
You can't do prefix based searches in SQL FTS. You might be able to store
your content in reverse and then do wildcard based searches, i.e.
select * from tablename where contains(ColumnName,'dcb*') which will return
hits to the reversed content of abcd, or 1bcd, but unfortunately also aabcd
reversed.
You may have to use like for this type of query.
Another option would be to use a thesaurus based search if you know in
advance all search tokens which you need to map to bdc, and expand then to
search on bcd,
so bcd will expand to a search on bcd and abcd, and 1bcd.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"rom_ask_u" <nimrod4u@.gmail.com> wrote in message
news:1123138487.817248.51670@.g47g2000cwa.googlegro ups.com...
> Hi!
> I'm using FTS in MSSQL2000.
> 1. i have a string "bcd" and i want the results : "abcd" or "1bcd" but
> not "bcda" or "aabcd" (always from the second letter).
> 2. the search column is a long string. the begining of the string is
> more important that the end. i want the results found in the begining
> to get higher RANK so i can present them first.
> Please help.
>