SQL- How do you retrieve records matching all values in a linked table.

Question

I have a many to many relationship within my database. For example I have a USER table, a ROLE Table, and USERINROLE table. I have a search on my website that needs to find users in specified roles. For example I would like to retrieve User records who are in roles "reader" AND "writer"

My Query before the where looks like this:

SELECT * FROM User u INNER JOIN UserInRole ur ON
u.UserId= ur.UserId INNER JOIN Role r ON 
Ur.RoleId = r.RoleId

the WHERE would be something like

WHERE roleid IN (1,2) 

but that brings users in role 1 OR role 2 and I need them to be both Role 1 AND role 2

I need to retrieve the user row and the role row together for the ORM (Nhibernate)

Edit: I am using NHibernate so if there is a native way to do this, that would be awesome

Answer 1

Join a second copy of UserInRole. Say the alias for the second copy is ur2, then your where condition can be

Where ur.roleId = 1 and ur2.roleId = 2

Answer 2

Couldn't you try something like this:

Select * from User u
inner join UserInRole ur1 on u.UserID = ur1.UserID
inner join UserInRole ur2 on u.UserID = ur2.UserID
where ur1.RoleID = 1
and ur2.RoleID = 2

Untested and unoptimised...

Answer 3

You can also use the INTERSECT operator for this.

SELECT * FROM User
WHERE UserId IN
(

SELECT UserId FROM UserInRole 
WHERE RoleId =1
INTERSECT
SELECT UserId FROM UserInRole 
WHERE RoleId =2
)