Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Many queries reference one subquery

Author  Topic 

ElCapitan
Starting Member

28 Posts

Posted - 2009-03-20 : 06:51:20
Hi all,
I have a few tables that I need the results from that are filtered on the results of a subquery.

SELECT RuleID FROM tblA WHERE tblA.ID IN (SELECT RuleID FROM tblRules WHERE RuleTypeID=2)
UNION
SELECT RuleID FROM tblB WHERE tblB.ID IN (SELECT RuleID FROM tblRules WHERE RuleTypeID=2)
UNION
SELECT RuleID FROM tblC WHERE tblC.ID IN (SELECT RuleID FROM tblRules WHERE RuleTypeID=2)

As you can see I use the same subquery 3 times. Even though the subquery returns exactly the same results, does the subquery get executed 3 seperate times? If it does it seems a waste of processing. So, is there a way to execute the subquery once, then have all subsequent SELECT statements reference the resultset of the subquery?

Thanks for your time.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-20 : 07:46:21
[code]Select
S.RuleId
from
(SELECT RuleID FROM tblA
UNION
SELECT RuleID FROM tblB
UNION
SELECT RuleID FROM tblC ) S
WHERE
Exists (SELECT * FROM tblRules WHERE RuleTypeID=2 and RuleID=S.[ID])[/code]
Go to Top of Page
   

- Advertisement -