How to get an id from the results in two tables
- by Chris Lively
Consider an order. An order will have one or more line items. Each line item is for a particular product.
Given a filter table with a couple of products, how would I get the order id's that had at least all of the products listed in the second table?
table Orders(
OrderId int
)
table LineItems (
OrderId int,
LineItemId int,
ProductId int
)
table Filter (
ProductId int
)
data
Orders
OrderId
--------
1
2
3
LineItems
OrderId LineItemId ProductId
------- ---------- ---------
1 1 401
1 2 502
2 3 401
3 4 401
3 5 603
3 6 714
Filter
ProductId
---------
401
603
Desired result of the query:
OrderId: 3
