mardi 23 février 2016

sql - find items that ALL users have in common

I can find the items common between two given users similar to the answer here:

SELECT items.name 
FROM   users 
       JOIN requests 
         ON users.user_id = requests.user_id 
       JOIN items 
         ON requests.item_id = items.item_id 
WHERE  users.name = 'jane'
INTERSECT
SELECT items.name 
FROM   users 
       JOIN requests 
         ON users.user_id = requests.user_id 
       JOIN items 
         ON requests.item_id = items.item_id 
WHERE  users.name = 'zaku';

I guess I could keep adding more intersect statements to include additional users and that's hardly a good solution. How would I find any and all item(s) common among ALL users? In my e.g., the common item among all users is "pc" but it could as well be any other item(s). See my code on SQL Fiddle. Thanks.

Aucun commentaire:

Enregistrer un commentaire