slack slack - 3 years ago 219
MySQL Question

MySQL query for selecting a or b but not c

I have a table with 3 columns: branchId, repoId and userId. I need to find users who created branch for repo 1 or repo 2 but not for repo 3. Any help will be much appreciated.

I tried doing the following:

select userId
from branch
where repoId=1
or repoId=2
and repoId<>3

However, this is also outputting userIds which have repoId 3 as other rows with that userId doesn't have repoId 3.

For example, for userid 2, there are 3 rows, with repoId 1, 2 and 3.

The output of the above query is still giving me result as userId 2 beacuse 2 of its rows dont have repoId 3.

Example table:

branchId repoId userId
1 1 1
2 2 1
3 1 2
4 2 2
5 3 2

So for this table, the result should be userId 1.

Answer Source

Try something like below.

select userId 
from branch 
where repoId in(1,2) AND userid not in(select userid from branch where repoId =3)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download