SQLite query to find set of orders containing a given set of customers

Question

A database has the following schema:

product:
  id: integer,
  model: string,
  ...
customer:
  id: integer,
  name: string,
  ...
order:
  product_id: integer,
  customer_id: integer,
  ...

I would like a list of all products which have been ordered by (at least) a given group of customers - that is, for a set of customers (A, B, C) I want a query that finds all X in the product database where there are order records [X, A], [X, B], [X, C]

For a given product I can get a list of orders by using a join, but I am not sure how to collect a set of orders and compare them to a list. I suppose one way to do it might be to have a chain of subqueries, one for each customer in the list, but that seems really ugly. The size of all the tables is potentially large. Is there a relatively good query to do this? Or maybe a better way to organize the tables? I'm a SQL newbie, sorry if I am overlooking something obvious. I did see this, which is not promising.

russell

Answer 1

Thanks for those who looked, I found an answer that looks OK. One other condition I didn't include is there are no duplicates in the order table, so this works (may be off since my actual database has different names, so the query is typed in by hand). Probably duplicate could be handled with a UNIQUE somewhere. If this is bad for any reason or there is a better way I'd appreciate a heads-up.

SELECT p.id, or.product_id, count() FROM orders or INNER JOIN product p ON p.id = or.product_id WHERE or.customer_id IN (1, 2, 3) GROUP BY or.customer_id HAVING COUNT() > 2;