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
)

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

Answers


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).


Need Your Help

Backbone: Use Model's Data and Functions in View

javascript backbone.js parse-platform model coffeescript

I'm fairly new to Backbone and was wondering how to access a model's data and functions from a view that injects the model as a dependency.

Hiding Row in DataGridView Very Slow

c# .net winforms datagridview

I have a DataGridView in a Winforms app that has about 1000 rows (unbound) and 50 columns. Hiding a column takes a full 2 seconds. When I want to hide about half the rows, this becomes a problem.