How to get an id from the results in two tables
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 )
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
davek is close. You first have to narrow down your result set to only include items matching the filter table, then obtain your result by count:
select orderId from lineitems where ProductId in (select productId from filter) group by orderid having count(distinct productid) = (select count(distinct productid) from filter)
or using joins instead of in:
select orderId from lineitems li inner join filter f on li.productId = f.productid group by orderid having count(distinct li.productid) = (select count(distinct productid) from filter)
I ran both through QA and they perform the same, but with a decent dataset, I'd presume the join will perform better.
select orderid from lineitems group by orderid having count(distinct productid) >= (select count(distinct productid) from filter)
might work (not sure about the having term, as I can't test it on my home box).