How to Select Distinct User IDs with Multiple Role IDs in SQL?

Retrieving User IDs Meeting Specific Role ID Criteria Across Multiple Rows

This article addresses the challenge of selecting unique user IDs that satisfy multiple role ID conditions spread across different rows within a database table. Let's consider a userrole table with userid and roleid columns:

userid	roleid
1	1
1	2
1	3
2	1

The goal is to identify all distinct userid values associated with roleid values 1, 2, and 3. Using the example data, the expected output is userid 1. Two SQL approaches can achieve this.

Method 1: Aggregate Query with HAVING Clause

This method utilizes an aggregate function and a HAVING clause:

SELECT userid
FROM userrole
WHERE roleid IN (1, 2, 3)
GROUP BY userid
HAVING COUNT(DISTINCT roleid) = 3;

This query first filters rows where roleid is 1, 2, or 3. Then, it groups the results by userid and counts the number of distinct roleid values for each user using COUNT(DISTINCT roleid). The HAVING clause filters to only include users with exactly three distinct role IDs.

Method 2: Self-Join Query

A more efficient alternative, especially for larger datasets, is a self-join approach:

SELECT DISTINCT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid AND t2.roleid = 2
JOIN userrole t3 ON t1.userid = t3.userid AND t3.roleid = 3
WHERE t1.roleid = 1;

This query uses three self-joins: t1 selects users with roleid 1; t2 ensures they also have roleid 2; and t3 confirms they have roleid 3. DISTINCT ensures only unique userid values are returned.

The optimal approach (aggregate vs. join) depends on table size and data distribution. For scenarios with a low probability of matching rows, the self-join method tends to perform better.

The above is the detailed content of How to Select Distinct User IDs with Multiple Role IDs in SQL?. For more information, please follow other related articles on the PHP Chinese website!