CODEWITHSUNDEEP
sqlt-sqljoin
AdsWhite
AdsWhite

Reputation: 35

How to filter SQL join to records that have multiple matches only

Consider a scenario where users can have multiple roles with different organisations.

The tables are:

USER - has an Id column
ORGANISATION - has an Id column
ROLE - has an Id column
USERROLE - has a User_Id column, an Organisation_Id column and a Role_Id column

(So for example, User Bob can have roles 1, 2 and 3 with ABC Inc and role 1 with XYZ Plc)

How do you only return users who have roles 1, 2 and 3? I don't mind if they have other roles as well or multiples of 1, 2 or 3 but they must at least have 1 of each role 1, 2 and 3.

Upvotes: 0

Views: 217

Answers (1)

Lamak
Lamak

Reputation: 70648

If it doesn't matter in which company they have those roles, then this should do:

SELECT [User_Id] 
FROM USERROLE UR
WHERE Role_Id IN (1,2,3)
GROUP BY [User_Id] 
HAVING COUNT(DISTINCT Role_Id) = 3

Upvotes: 3

Related Questions