How to Select User IDs with Specific Roles (1, 2, and 3) from a Table?

Retrieving User IDs with Multiple Roles (1, 2, and 3)

This guide demonstrates how to efficiently select unique user IDs possessing roles 1, 2, and 3 from a table with userid and roleid columns. We'll explore two effective SQL query methods.

Method 1: Aggregate Query with HAVING Clause

This approach uses aggregation and filtering:

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

The query filters for rows where roleid is 1, 2, or 3. It then groups the results by userid and uses HAVING COUNT(*) = 3 to ensure only users with all three roles are included.

Method 2: Self-JOIN Query

Alternatively, a self-JOIN can achieve the same result:

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

This query joins the userrole table to itself three times. It ensures that a userid has roleid 1, then joins to find the same userid with roleid 2 and finally roleid 3. The INNER JOIN ensures that only users with all three roles are returned. This method might be less efficient than the aggregate approach for very large tables.

The above is the detailed content of How to Select User IDs with Specific Roles (1, 2, and 3) from a Table?. For more information, please follow other related articles on the PHP Chinese website!