MySQL: Match all records of another table-CodePudding

I want to know all of the users that have bought all the specified products

Table items_list (which can evolve, so the number of entries can not be used to query)

 --------- 
| item_id |
 --------- 
|     101 |
|     200 |
|     512 |
 ---------

Table orders

 --------- --------- 
| item_id | user_id |
 --------- --------- 
|     101 |       1 |
|     200 |       1 |
|     512 |       1 |
|     200 |       2 |
|     512 |       2 |
|     101 |       3 |
|     200 |       3 |
 --------- ---------

When I do

SELECT * FROM orders o WHERE o.item_id IN (SELECT i.item_id FROM items_list i) GROUP BY user_id

I got all the users

I want only user_id 1, who has bought everything from items_list

CodePudding user response：

Well one of the methods can be grouping my user_id and then checking DISTINCT item_id:

SELECT 
  * 
FROM
  orders o 
GROUP BY o.`user_id` 
HAVING COUNT(DISTINCT(o.item_id)) = (SELECT  COUNT(*) FROM items_list)

COUNT(DISTINCT(o.item_id)) will give you All distinct items for the user and then (SELECT COUNT(*) FROM items_list) will give you the count of items in your table. Checking if the count matches should give you the result you are expecting