Search code examples
sqllistmatchrelational-division

SQL query on showing a grouped rows that contains all values from a list

SQL QUERIES

TABLE - CUSTOMER
1001,1
1001,2
1001,3
1002,1
1002,3
1003,3

TABLE - PRODUCT
1
2
3

The result should be 1001 coz it got all the values match the order table.
The PRODUCT table might change over time.
I want to find out who have bought all the product from me.
Thanks!

Solution

  • Assuming that CustomerTB is a table which has two columns: CustomerID and ProductID. You need to grouped it by CustomerID and count it's distinct product that he bought. (DISTINCT is not required if customer can only bought one per product) and should be equal to the total number of product in the product table's list.

    SELECT customerID
FROM   CustomerTB
GROUP BY CustomerID
HAVING COUNT(DISTINCT productID) = (SELECT COUNT(*) FROM ProductTB)