May 17, 2011 at 10:13 am
I think this is a pretty common problem, but I haven't found any solutions yet. I think part of the problem is I'm not quite sure what terms to use for the search.
I have a header record and a detail record. They have a one to many relationship from the header to the detail. The detail shows all the products purchased in a particular sale. All the products in that sale have the same header id. I need to find all the header records where someone purchased say a soda AND a hamburger. Finding all the records where someone purchased one or the other is easy and finding the results for the above example isn't difficult either. My problem is that I want to let the user ask to find all of 2 or 3 or 4 entries (maybe up to 5 or 6 combinations).
Here's some sample data and structure, simplified to make the whole thing more readable
CREATE TABLE #SalesDetails(
SaleID int,
ProductID int
)
CREATE TABLE #ProdList(ProductID int)
INSERT INTO #SalesDetails(SaleID, ProductID)
VALUES(1, 100),
(1, 200),
(1, 245),
(1, 370),
(2, 100),
(2, 200),
(2, 370),
(2, 500),
(3, 100),
(3, 200),
(3, 370),
(3, 284),
(3, 500),
(4, 100),
(4, 200),
(4, 370),
(4, 284),
(5, 200),
(5, 500),
(6, 200),
(7, 100),
(7, 284),
(7, 500),
(7, 374),
(7, 370),
(7, 245),
(7, 540),
(8, 245),
(8, 370),
(8, 500)
INSERT INTO #ProdList(ProductID)
VALUES(100), (200)
Now, if I want to just search for all the ones with 2 products, that's easy
SELECT SaleID
FROM #SalesDetails
WHERE SaleID IN (SELECT SaleID FROM #SalesDetails WHERE ProductID = 100)
AND SaleID IN (SELECT SaleID FROM #SalesDetails WHERE ProductID = 200)
GROUP BY SaleID
If I have a static number of products to search on, then I just do that many IN selects. The results are reasonably fast on the large database I have and I'm happy with those results. The problem is if I want to allow people select by as many records as end up in the #ProdList table. I'd programmatically limit it to between 1 and 5 products, but it could be any number of those.
I'm using the method shown here (http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/comment-page-1/#comment-1644) to create a catch-all query, so I could just use a cursor or other loop to go through the @ProdList table and add as many Where SaleID IN statements as I need, but I'd rather avoid that if I can.
Is there a way to do this without resorting to a cursor?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 17, 2011 at 10:30 am
Can a productID exist multiple times for a single SaleID? If not, you could do something like the below:
SELECT SaleID FROM #SalesDetails sd
INNER JOIN #ProdList ON sd.ProductID = #ProdList.ProductID
GROUP BY SaleID
HAVING COUNT(*)=(SELECT COUNT(*) FROM #ProdList)
If there can be multiple occurences of a productID for each saleID, you'd first need to get a distinct list.
May 17, 2011 at 10:34 am
HowardW (5/17/2011)
Can a productID exist multiple times for a single SaleID? If not, you could do something like the below:
SELECT SaleID FROM #SalesDetails sdINNER JOIN #ProdList ON sd.ProductID = #ProdList.ProductID
GROUP BY SaleID
HAVING COUNT(*)=(SELECT COUNT(*) FROM #ProdList)
If there can be multiple occurences of a productID for each saleID, you'd first need to get a distinct list.
Yes, there can be multiples of each prodID
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 17, 2011 at 10:38 am
Something like this should do:
;WITH Sales AS (
SELECT SaleId, COUNT(DISTINCT ProductId) AS DistinctProducts
FROM #SalesDetails
WHERE ProductId IN (SELECT ProductId FROM #ProdList)
GROUP BY SaleId
),
Products AS (
SELECT COUNT(DISTINCT ProductId) AS DistinctProducts
FROM #ProdList
)
SELECT SaleID
FROM Sales AS A
WHERE DistinctProducts = (SELECT DistinctProducts FROM Products)
-- Gianluca Sartori
May 17, 2011 at 10:52 am
Gianluca Sartori (5/17/2011)
Something like this should do:
;WITH Sales AS (
SELECT SaleId, COUNT(DISTINCT ProductId) AS DistinctProducts
FROM #SalesDetails
WHERE ProductId IN (SELECT ProductId FROM #ProdList)
GROUP BY SaleId
),
Products AS (
SELECT COUNT(DISTINCT ProductId) AS DistinctProducts
FROM #ProdList
)
SELECT SaleID
FROM Sales AS A
WHERE DistinctProducts = (SELECT DistinctProducts FROM Products)
I'd been thinking a CTE might do it, but I wasn't sure how to set it up, thanks! I'll try this out.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 17, 2011 at 10:54 am
Well, actually you don't need a CTE, but you could tweak Howard's code to take the distinct into account:
SELECT SaleID FROM #SalesDetails sd
INNER JOIN #ProdList ON sd.ProductID = #ProdList.ProductID
GROUP BY SaleID
HAVING COUNT(DISTINCT ProductId)=(SELECT COUNT(DISTINCT ProductId) FROM #ProdList)
-- Gianluca Sartori
May 17, 2011 at 10:58 am
Gianluca Sartori (5/17/2011)
Well, actually you don't need a CTE, but you could tweak Howard's code to take the distinct into account:
SELECT SaleID FROM #SalesDetails sd
INNER JOIN #ProdList ON sd.ProductID = #ProdList.ProductID
GROUP BY SaleID
HAVING COUNT(DISTINCT ProductId)=(SELECT COUNT(DISTINCT ProductId) FROM #ProdList)
Yeah, I'm going to play with that as well when I have a chance. I just have to wrap my head around the count = count part and make sure there's no way that'll return false positives or miss some results.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 17, 2011 at 11:13 am
Stefan,
I've actually had to do that at my work place. If those solutions don't work for some reason, let us know and I'll check my production environment to see what it was I did.
May 17, 2011 at 11:13 am
Drat, I'm too slow. Here's the one I came up with though. Pretty much the same, except I assumed you would be passing the list in as a parameter and wouldn't want to loop to build the temp table so I used XML. If you can pass the parameters in as XML, you can skip the step where I build the XML string.DECLARE @varcharProducts varchar(max)
, @ProductCount int
, @Products xml
SELECT @varcharProducts = '100,200'
SELECT @Products = '<ProductID>' + REPLACE(@varcharProducts, ',','</ProductID><ProductID>') + '</ProductID>'
SELECT @ProductCount = COUNT(*) FROM @Products.nodes('ProductID') AS SelectedProducts(Products)
SELECT SaleID
FROM #SalesDetails
INNER JOIN @Products.nodes('ProductID') AS SelectedProducts(Products) ON Products.query('.').value('ProductID[1]', 'int') = #SalesDetails.ProductID
GROUP BY SaleID
HAVING COUNT(DISTINCT ProductID) = @ProductCount
Thanks,
Chad
May 17, 2011 at 11:24 am
Brandie Tarvin (5/17/2011)
Stefan,I've actually had to do that at my work place. If those solutions don't work for some reason, let us know and I'll check my production environment to see what it was I did.
Awesome, I think one or both of those should do the trick, but if they don't I'll ask.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 17, 2011 at 11:25 am
Chad Crawford (5/17/2011)
Drat, I'm too slow. Here's the one I came up with though. Pretty much the same, except I assumed you would be passing the list in as a parameter and wouldn't want to loop to build the temp table so I used XML. If you can pass the parameters in as XML, you can skip the step where I build the XML string.DECLARE @varcharProducts varchar(max), @ProductCount int
, @Products xml
SELECT @varcharProducts = '100,200'
SELECT @Products = '<ProductID>' + REPLACE(@varcharProducts, ',','</ProductID><ProductID>') + '</ProductID>'
SELECT @ProductCount = COUNT(*) FROM @Products.nodes('ProductID') AS SelectedProducts(Products)
SELECT SaleID
FROM #SalesDetails
INNER JOIN @Products.nodes('ProductID') AS SelectedProducts(Products) ON Products.query('.').value('ProductID[1]', 'int') = #SalesDetails.ProductID
GROUP BY SaleID
HAVING COUNT(DISTINCT ProductID) = @ProductCount
Thanks,
Chad
Thanks for still posting. I like to gather lots of possibilities both to see which one or combination works best/fastest and because sometimes they combine to suggest new possibilities.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 17, 2011 at 11:26 am
Chad Crawford (5/17/2011)
Drat, I'm too slow. Here's the one I came up with though. Pretty much the same, except I assumed you would be passing the list in as a parameter and wouldn't want to loop to build the temp table so I used XML. If you can pass the parameters in as XML, you can skip the step where I build the XML string.DECLARE @varcharProducts varchar(max), @ProductCount int
, @Products xml
SELECT @varcharProducts = '100,200'
SELECT @Products = '<ProductID>' + REPLACE(@varcharProducts, ',','</ProductID><ProductID>') + '</ProductID>'
SELECT @ProductCount = COUNT(*) FROM @Products.nodes('ProductID') AS SelectedProducts(Products)
SELECT SaleID
FROM #SalesDetails
INNER JOIN @Products.nodes('ProductID') AS SelectedProducts(Products) ON Products.query('.').value('ProductID[1]', 'int') = #SalesDetails.ProductID
GROUP BY SaleID
HAVING COUNT(DISTINCT ProductID) = @ProductCount
Thanks,
Chad
Oh, and I'm currently using a table type parameter to pass the values.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 18, 2011 at 12:17 am
I know I'm late to the party, but I was reading the question and I'm partially lost, in that I'm not sure what your expected results are based on the sample data. Are you looking for all sales where a soda and a hamburger were purchased or just a soda and a hamburger?
May 18, 2011 at 1:04 am
Lynn Pettis (5/18/2011)
I know I'm late to the party, but I was reading the question and I'm partially lost, in that I'm not sure what your expected results are based on the sample data. Are you looking for all sales where a soda and a hamburger were purchased or just a soda and a hamburger?
+ 1 ... Need "Expected Results" to do any coding 🙂 I think i have a way to do this 🙂
May 18, 2011 at 6:50 am
Seems more like a data mining model than a query.
Why is it that they want to see this?
Just a thought....
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply