How to Find Person IDs Associated with All Stuff IDs in MySQL?

Check if a Column Contains All Values of Another Column in MySQL

Problem:

Suppose you have two tables, T1 and T2, with columns representing people IDs and stuff IDs. How can you determine which person IDs are associated with all the stuff IDs in T2?

Solution:

To find the person IDs that have all associated stuff IDs found in T2, follow these steps:

1. Use a nested query to select the person IDs from T1 that match any stuff ID in T2:

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

2. Group the results by person ID and check which entries contain all the stuff IDs in T2:

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

3. Combine these queries to get the final result:

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

This query will return the person IDs that have all associated stuff IDs specified in T2. In the example provided, the result would be person ID 1.

The above is the detailed content of How to Find Person IDs Associated with All Stuff IDs in MySQL?. For more information, please follow other related articles on the PHP Chinese website!