July 14, 2010 at 2:03 am
Hi Folks
The uncharacteristically hot summer we’re having in the UK has addled my brains, and I’m struggling with a query that I’m sure is very straightforward.
Using a simplified OrderItems table and the following data to represent my problem:
OrderID Product
10001 Bananas
10002 Apples
10002 Pears
10002 Oranges
10003 Bananas
10004 Apples
10004 Pears
I need to return distinct OrderIDs where the following conditions are met:
• The order is just for Bananas (ie just the one row exists for an OrderID)
OR
• The order is for Apples, Pears and Oranges (ie the three rows exist for an OrderID).
A query on the above data using these criteria would yield 10001, 10002 and 10003 but not 10004. My real table contains 250,000 rows however performance isn’t a key issue here as the query will run once a week during an off-peak period.
Many thanks in advance to anyone who can point me in the right direction!
Cheers - Graham
July 14, 2010 at 3:11 am
Hi,
i understood that u wanna get distinct order id and its records huh?
u mean ur output is 1,2,3,4?
Regards,
Gayathri 🙂
July 14, 2010 at 3:20 am
This is what i could come up with.
DECLARE@tbl_OrderItems TABLE
(
OrderID INT,
Product VARCHAR(100)
)
INSERT@tbl_OrderItems
SELECT10001, 'Bananas' UNION ALL
SELECT10002, 'Apples' UNION ALL
SELECT10002, 'Pears' UNION ALL
SELECT10002, 'Oranges' UNION ALL
SELECT10003, 'Bananas' UNION ALL
SELECT10004, 'Apples' UNION ALL
SELECT10004, 'Pears'
SELECTDISTINCT OrderID
FROM@tbl_OrderItems Ord
WHERE(
Product = 'Bananas'
AND NOT EXISTS( SELECT * FROM @tbl_OrderItems InnOrd WHERE InnOrd.OrderID = Ord.OrderID AND Product != 'Bananas' )
)
OROrderID IN
(
SELECTOrderID
FROM@tbl_OrderItems
WHEREProduct IN ( 'Apples', 'Pears', 'Oranges' )
GROUP BY OrderID
HAVING COUNT(*) = 3
)
One more simpler looking solution would be this
SELECTOrderID
FROM@tbl_OrderItems Ord
WHERE(
Product = 'Bananas'
AND OrderID NOT IN ( SELECT OrderID FROM @tbl_OrderItems WHERE Product != 'Bananas' )
)
UNION ALL
SELECTOrderID
FROM@tbl_OrderItems
WHEREProduct IN ( 'Apples', 'Pears', 'Oranges' )
GROUP BY OrderID
HAVING COUNT(*) = 3
Check which one works better for you
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 14, 2010 at 4:22 am
/*
I need to return distinct OrderIDs where the following conditions are met:
• The order is just for Bananas (ie just the one row exists for an OrderID)
• The order is for Apples, Pears and Oranges (ie the three rows exist for an OrderID).
*/
-- make some sample data
DROP TABLE #OrderItems
CREATE TABLE #OrderItems (OrderID INT, Product VARCHAR(100))
INSERT INTO #OrderItems
SELECT 10001, 'Bananas' UNION ALL
SELECT 10002, 'Apples' UNION ALL
SELECT 10002, 'Pears' UNION ALL
SELECT 10002, 'Oranges' UNION ALL
SELECT 10003, 'Bananas' UNION ALL
SELECT 10004, 'Apples' UNION ALL
SELECT 10004, 'Pears' UNION ALL
SELECT 10005, 'Bananas' UNION ALL
SELECT 10005, 'Apples'
-- generate some results
SELECT h.OrderID
FROM #OrderItems h
INNER JOIN (
SELECT OrderID, OrderItems = COUNT(*)
FROM #OrderItems
GROUP BY OrderID
) l1 ON l1.OrderID = h.OrderID
LEFT JOIN (
SELECT OrderID, OrderItems = COUNT(*)
FROM #OrderItems
WHERE Product IN ('Apples', 'Pears', 'Oranges')
GROUP BY OrderID
) l2 ON l2.OrderID = h.OrderID
WHERE (h.Product = 'Bananas' AND l1.OrderItems = 1)
OR l2.OrderItems = 3
GROUP BY h.OrderID
-- if multiple rows exist for Apples, Pears or Oranges against any order
-- then the dupes would have to be eliminated in derived table l2
-- or:
-- generate some results
;WITH CountedOrderItems AS (
SELECT *,
OrderItems = COUNT(Product) OVER (PARTITION BY OrderID)
FROM #OrderItems
)
SELECT h.OrderID
FROM CountedOrderItems h
OUTER APPLY (
SELECT OrderID
FROM CountedOrderItems
WHERE OrderID = h.OrderID
AND OrderItems = 3
AND Product IN ('Apples', 'Pears', 'Oranges')
GROUP BY OrderID) iTVF
WHERE (h.Product = 'Bananas' AND h.OrderItems = 1)
OR iTVF.OrderID IS NOT NULL
GROUP BY h.OrderID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 14, 2010 at 9:11 am
Kingston and Chris: many thanks for your responses - absolutely awesome. The level of help offered in these forums is quite extraordinary.
Gayathri: did the responses offered by Kingston and Chris help clarify my question?
Thanks again guys!
Cheers - Graham
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply