Efficient query INNER JOINing on same table

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)

Efficient query INNER JOINing on same table

Author

Topic

ElCapitan
Starting Member

28 Posts

Posted - 2009-01-08 : 07:09:49

Hi all,
I am looking to find out the best way to run a particular query to give optimum performance and any help will be appreciated.

I have a table...

tblRule
---------
RuleID PK
PriceMin
PriceMax
ProductID

I need to list all the records where the records filtered on ProductID IN (1,2,3) have a PriceMin greater than the PriceMin of filtered records where ProductID IN (4,5,6). I hope this makes sense :-)

I have so far...
SELECT DISTINCT tblRule.RuleID, tblRule.PriceMin
FROM tblRule INNER JOIN
(SELECT PriceMin
FROM tblRule AS tblRule_1
WHERE (RuleID IN (4,5,6))) AS tblRuleUser
ON tblRule.PriceMin > tblRuleUser.PriceMin
WHERE (tblRule.RuleID IN (1, 2, 3))

If I execute this without an index on the PriceMin, I get the same query plan with an index on PriceMin.

Is this the most efficient way to grab the recordset or are there other ways?
Your help is greatly appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 07:55:42

Maybe this?

SELECT		RuleID
FROM		tblRule
GROUP BY	RuleID
HAVING		MAX(CASE WHEN ProductID IN (1, 2, 3) THEN PriceMin ELSE NULL END) > MIN(CASE WHEN ProductID IN (4, 5, 6) THEN PriceMin ELSE NULL END)

E 12°55'05.63"
N 56°04'39.26"

ElCapitan
Starting Member

28 Posts

Posted - 2009-01-08 : 10:46:55

Thanks for the reply. Although this won't give me quite what I need, it does offer me another direction.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources