Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    3

    Unanswered: 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.

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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 Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Aug 2004
    Posts
    3

    Problem Solved

    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.

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •