How to Check if a MySQL Column Contains All Values from Another Column?

Check if a Column Contains ALL the Values of Another Column in a MySQL Database

Consider two tables, T1 and T2, with the following structures:

T1:

personID	stuffID
1	1
1	2
1	3
1	4
2	1
2	4
3	1
3	2

T2:

stuffID

1

2

3

The goal is to retrieve all personID values from T1 that are associated with every stuffID value in T2. For this example, the result would be 1 because only personID 1 has records for all three stuffIDs present in T2.

Solution:

To achieve this, we can use a two-step process:

Step 1: Identify matching records

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

This subquery retrieves all personID values from T1 that have at least one matching stuffID in T2.

Step 2: Check for completeness

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

This part groups the results from Step 1 by personID and checks if the count of distinct stuffID values for each group is equal to the number of stuffID values in T2. If so, it indicates that personID is associated with all stuffID values from T2.

Final Query:

Combining both steps, the final query to check if a column contains all the values of another column in MySQL is:

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

The above is the detailed content of How to Check if a MySQL Column Contains All Values from Another Column?. For more information, please follow other related articles on the PHP Chinese website!