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 w/ query

I need help typing to pull info out of a SMS database. We are selecting info
from three table. we inner join the tables to get selected info out. where
we are having trouble is by limiting what is returned. is there a way to say
"if the row has a specific character(s) then the row will be ignored". the
way it is now gives us about ten time the data, because the machine name is
associated with multiple collections within sms.
On Tue, 1 Feb 2005 19:51:01 -0800, Greg wrote:
(snip)
>is there a way to say
>"if the row has a specific character(s) then the row will be ignored".
Hi Greg,
Yes, there is.
Unfortunately, I can't give more specific help, since you didn't post any
specific information about your requirements. In order to help you, I need
to know the structure of your tables (as CREATE TABLE statements, omitting
irrelevant columns but including all constraints and properties), some
sample data (as INSERT statements) and expected output.
Consult www.aspfaq.com/5006, then post back with more details if you need
further help.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

help- table record not selected if only column names specfied

we have a SQL 2k Database of size 2 GB. when we select data from a table using * it is returning the record.
Query is "select * from customermaster where ID=5"
result 1 record selected
if only column specified then it is not returning the any data.
Query is "select customername from customermaster where ID=5"
reult zero record selected
when the same table recreated and data transferred then it is working fine
could you please tell us what is the problem and how to identify the table is having this type of problem.
Thanking You
Hi,
Can you execute the below commands in query analyzer forcustomermaster
table
use dbname
go
dbcc checktable('customermaster')
go
sp_spaceused 'customermaster',@.updateusage='true'
go
select customername from customermaster where ID=5
Thanks
Hari
MCDBA
"Narayan Saran" <Narayan Saran@.discussions.microsoft.com> wrote in message
news:2D6790EA-8717-45BF-B76D-AF5B64C3AF8D@.microsoft.com...
> we have a SQL 2k Database of size 2 GB. when we select data from a table
using * it is returning the record.
> Query is "select * from customermaster where ID=5"
> result 1 record selected
> if only column specified then it is not returning the any data.
> Query is "select customername from customermaster where ID=5"
> reult zero record selected
> when the same table recreated and data transferred then it is working fine
> could you please tell us what is the problem and how to identify the table
is having this type of problem.
> Thanking You
>
>
>

help- table record not selected if only column names specfied

we have a SQL 2k Database of size 2 GB. when we select data from a table us
ing * it is returning the record.
Query is "select * from customermaster where ID=5"
result 1 record selected
if only column specified then it is not returning the any data.
Query is "select customername from customermaster where ID=5"
reult zero record selected
when the same table recreated and data transferred then it is working fine
could you please tell us what is the problem and how to identify the table i
s having this type of problem.
Thanking YouHi,
Can you execute the below commands in query analyzer forcustomermaster
table
use dbname
go
dbcc checktable('customermaster')
go
sp_spaceused 'customermaster',@.updateusage='true'
go
select customername from customermaster where ID=5
Thanks
Hari
MCDBA
"Narayan Saran" <Narayan Saran@.discussions.microsoft.com> wrote in message
news:2D6790EA-8717-45BF-B76D-AF5B64C3AF8D@.microsoft.com...
> we have a SQL 2k Database of size 2 GB. when we select data from a table
using * it is returning the record.
> Query is "select * from customermaster where ID=5"
> result 1 record selected
> if only column specified then it is not returning the any data.
> Query is "select customername from customermaster where ID=5"
> reult zero record selected
> when the same table recreated and data transferred then it is working fine
> could you please tell us what is the problem and how to identify the table
is having this type of problem.
> Thanking You
>
>
>

help- table record not selected if only column names specfied

we have a SQL 2k Database of size 2 GB. when we select data from a table using * it is returning the record.
Query is "select * from customermaster where ID=5"
result 1 record selected
if only column specified then it is not returning the any data.
Query is "select customername from customermaster where ID=5"
reult zero record selected
when the same table recreated and data transferred then it is working fine
could you please tell us what is the problem and how to identify the table is having this type of problem.
Thanking YouHi,
Can you execute the below commands in query analyzer forcustomermaster
table
use dbname
go
dbcc checktable('customermaster')
go
sp_spaceused 'customermaster',@.updateusage='true'
go
select customername from customermaster where ID=5
--
Thanks
Hari
MCDBA
"Narayan Saran" <Narayan Saran@.discussions.microsoft.com> wrote in message
news:2D6790EA-8717-45BF-B76D-AF5B64C3AF8D@.microsoft.com...
> we have a SQL 2k Database of size 2 GB. when we select data from a table
using * it is returning the record.
> Query is "select * from customermaster where ID=5"
> result 1 record selected
> if only column specified then it is not returning the any data.
> Query is "select customername from customermaster where ID=5"
> reult zero record selected
> when the same table recreated and data transferred then it is working fine
> could you please tell us what is the problem and how to identify the table
is having this type of problem.
> Thanking You
>
>
>