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.