Query Pulling Users Back Which Don't Meet Criteria of WHERE Statement

Question

I'm retrieving users from the database to display in a dropdown list. I need all the users that match the correct group id and that are also in a one of two particular roles. Here is the statement I have so far:

SELECT a.USER_ID AS userid, a.GROUP_ID, a.ROLE, 
b.ID, b.NAME, b.LAST_NAME FROM b_sonet_user2group a 
INNER JOIN b_user b ON a.USER_ID = b.ID 
WHERE a.GROUP_ID = $groupid 
AND a.ROLE = 'A' OR a.ROLE = 'E'

This is pulling in users that aren't even in the group though and I have outputted $groupid and it is showing the correct number. Am I doing something wrong with the WHERE section of this query? I'm also getting a repeat of one user when they only appear once in the user list which meets that criteria. If I'm missing something simple here let me know.

Thanks

Answer 1

Can you try this:

SELECT a.USER_ID AS userid, a.GROUP_ID, a.ROLE, 
b.ID, b.NAME, b.LAST_NAME FROM b_sonet_user2group a 
INNER JOIN b_user b ON a.USER_ID = b.ID 
WHERE a.GROUP_ID = $groupid 
AND (a.ROLE = 'A' OR a.ROLE = 'E')

Hope it helps..

Answer 2

You have to use IN Clause instead on OR.

Try this:

SELECT a.USER_ID AS userid, a.GROUP_ID, a.ROLE, b.ID, b.NAME, b.LAST_NAME 
FROM b_sonet_user2group a 
INNER JOIN b_user b ON a.USER_ID = b.ID 
WHERE a.GROUP_ID = $groupid AND a.ROLE IN ('A', 'E');