why two joins on the same table ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

why two joins on the same table ?

Author

Topic

BitShift
Yak Posting Veteran

98 Posts

Posted - 2008-04-10 : 18:45:32

I was looking at some sample queries on how to do product promotion. Basically im after the top few products that have been ordered along with a given product ID. I think this should do it, but wasnt sure about the 2nd join...and why ?


SELECT
ProductID,
ProductName,
SUBSTRING(Description, 1, 150) + '...'
AS Description
FROM Products
WHERE ProductID IN
(
SELECT TOP 5 details2.ProductID
FROM OrderDetails details1
INNER JOIN OrderDetails details2
ON details1.OrderID = details2.OrderID
WHERE details1.ProductID = @ProductID
AND details2.ProductID != @ProductID
GROUP BY details2.ProductID
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 18:50:30

Number of times ordered or number of items (sum of items) for productid?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 19:01:54

[code]SELECT TOP 5 d.ProductID,
p.ProductName,
p.Description
FROM (
SELECT ProductID,
COUNT(*) AS theTimes
FROM OrderDetails
GROUP BY OrderID
HAVING MAX(CASE WHEN ProductID = @ProductID THEN 1 ELSE 0 END) = 1
) AS d
INNER JOIN (
SELECT ProductID,
ProductName,
SUBSTRING(Description, 1, 150) + '...' AS Description
FROM Products
) AS p ON p.ProductID = d.ProductID
WHERE d.ProductID <> @ProductID
GROUP BY d.ProductID,
p.ProductName,
p.Description
ORDER BY SUM(d.theTimes) DESC[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources