CODEWITHSUNDEEP
mysqlsql
Andy
Andy

Reputation: 503

Multiple and on INNER JOIN?

I have a query like this:

SELECT Email 
FROM minisecurity.my_aspnet_membership m         
INNER JOIN minisecurity.my_aspnet_details t ON t.id = m.userId         
INNER JOIN minisecurity.my_aspnet_usersinroles r ON m.userId = r.userId and r.roleId = '7'         
WHERE t.customerid = '2'

On my second INNER JOIN I want to filter so that I get a result where roleId is either 6 or 7.

I tried both 

INNER JOIN minisecurity.my_aspnet_usersinroles r
    ON m.userId = r.userId and r.roleId = '7' and r.roleId = '6'

and 

INNER JOIN minisecurity.my_aspnet_usersinroles r 
    ON m.userId = r.userId and r.roleId = '7' or r.roleId = '6'

but both returns wrong result.

How is this done correctly?

Upvotes: 0

Views: 39

Answers (2)

Zafar Malik
Zafar Malik

Reputation: 6844

Check below query-

SELECT Email 
FROM minisecurity.my_aspnet_membership m         
INNER JOIN minisecurity.my_aspnet_details t ON t.id = m.userId         
INNER JOIN minisecurity.my_aspnet_usersinroles r ON m.userId = r.userId       
WHERE t.customerid = 2 and r.roleId in (6,7);

you can also use OR but in is more optimized than OR-

SELECT Email 
FROM minisecurity.my_aspnet_membership m         
INNER JOIN minisecurity.my_aspnet_details t ON t.id = m.userId         
INNER JOIN minisecurity.my_aspnet_usersinroles r ON m.userId = r.userId       
WHERE t.customerid = 2 and (r.roleId=6 OR r.roleId=7);

Note: As you are using inner join, so you can use additional condition either with on clause or in where clause.

Thanks @strawberry for editing to make answer better.

@Andy: Strawberry removed '' after considering that these fields are integer type and not char/varchar type, but if any field is not integer then must use ''.

Upvotes: 1

guido
guido

Reputation: 19194

AND has precedence over OR, so what you want is:

 INNER JOIN ... ON m.userId = r.userId and (r.roleId = '7' or r.roleId = '6')

Upvotes: 0

Related Questions