sql - check if a column contains ALL the values of another column - Mysql

Question

Let's suppose I have a table T1 with people IDs and other stuff IDs, as the following

Table: T1
personID | stuffID 
    1    |    1
    1    |    2
    1    |    3
    1    |    4
    2    |    1
    2    |    4
    3    |    1
    3    |    2

And another table T2 with just one column of stuffIDs

Table: T2
stuffID
   1  
   2  
   3  

The result that I would get, by a SELECT, is a table of peopleIDs who are connected with ALL the stuffIDs of T2.

Following the example the result would be only the id 1 (the personID 3 has no to appear even if all the stuffIDs it is associated are included in T2.stuffID).

Answer 1

If I understand correctly, you want to retrieve all the personID's from T1 that have all associated stuffID's found in T2.

You can break this up as follows: First of all, find all the T1 entries that match with a nested query

SELECT personID 
FROM T1 WHERE stuffID IN (SELECT stuffID FROM t2)

Now you need to check which of the entries in this set contains ALL the stuffID's you want

GROUP BY personID
HAVING COUNT(DISTINCT stuffID) = (SELECT COUNT(stuffID) FROM t2)

and put it all together:

SELECT personID 
FROM T1 WHERE stuffID IN (SELECT stuffID FROM t2)
GROUP BY personID
HAVING COUNT(DISTINCT stuffID) = (SELECT COUNT(stuffID) FROM t2)

HTH.