Best way to write query that shows orders that\'ve purchased 2 specific products?

Question

Best way to write query that shows orders that\'ve purchased 2 specific products?

Goalie35

SSC Eights!

Points: 871
More actions
September 20, 2019 at 1:13 pm

#3681887
Basically, I just want to write a cleaner query than what I have, which involved setting up 2 temp tables. I'm reviewing orders placed in our company. Our OrderDetails table contains 2 key fields:
- the order id
- the product id that was ordered
So obviously, there will be one record in this table for each product purchased.
I want to create a query that returns the order id's of all orders that've purchased ProductId =1 AND ProductId = 2.
OrderDetails
OrderDetailsId OrderId ProductId

1 123 1
2 456 1
3 456 3
4 789 1
5 789 2
6 789 3
In the above scenario, only orderId 789 should be returned because it's the only order that contains both productId's 1 & 2.
Thanks for any help you can provide.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Andreas P. Williams SSCrazy Points: 2487 More actions · Answer 1

I would go for

select OrderId from OrderDetails where ProductId=1
intersect
select OrderId from OrderDetails where ProductId=2;

drew.allen SSC Guru Points: 76985 More actions · Answer 2

Since you didn't provide sample data IN A CONSUMABLE FORMAT, you're getting untested code. This might perform better.

SELECT OrderID
FROM OrderDetails
WHERE ProductID IN (1, 2)
GROUP BY OrderID
HAVING COUNT(DISTINCT ProductID) = 2;

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

drew is correct (I mean his code is the right way to do it)

This reply was modified 5 years, 2 months ago by MVDBA (Mike Vessey).

MVDBA

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

although in your example order 789 contains 3 items, do you only want where there are 2 items? both of which are 1 and 2? or if you have 50 items (including 1 and 2) is that acceptable?

MVDBA

drew.allen SSC Guru Points: 76985 More actions · Answer 5

MVDBA (Mike Vessey) wrote:

although in your example order 789 contains 3 items, do you only want where there are 2 items? both of which are 1 and 2? or if you have 50 items (including 1 and 2) is that acceptable?

His original description already said that 789 should be returned. The extra item(s) clearly do not matter.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Goalie35 SSC Eights! Points: 871 More actions · Answer 6

or if you have 50 items (including 1 and 2) is that acceptable?

Correct. Even though order id 789 contains 3 items, it should be included because Product ID's 1 & 2 are included

Thanks again!