CODEWITHSUNDEEP
sqlsqlite
Simon Solnes
Simon Solnes

Reputation: 61

Counting the amount of relations to one table to another

Very hard to create a good title for this.

Given the table products

productID
---------
892
583
388

And the table purchases

customerID   productID
----------   ---------
56           892
97           388
56           583
56           388
97           583

How would I go about getting a table of all the costumers that have bought all products?

Upvotes: 0

Views: 27

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Use GROUP BY clause with HAVING :

SELECT pr.customerID 
FROM products p INNER JOIN
     purchases pr
     on pr.productID = p.productID
GROUP BY pr.customerID 
HAVING COUNT(DISTINCT pr.productID) = (SELECT COUNT(*) FROM products);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270623

You can use group by and having:

select customerId
from purchases
group by customerId
having count(distinct productID) = (select count(*) from products);

Upvotes: 2

Related Questions