CODEWITHSUNDEEP
sql
Vadim
Vadim

Reputation: 137

Select rows by a condition: a and (b xor c)

I have 3 tables like these:

User:
Id | Name

Role:
Id | Name

User2Role:
RoleId | UserId | RoleName

Is there a way to select users who have RoleName 'A' and ('B' xor 'C'), so it's AB or AC only, not BC not just A etc., in one query without subqueries?

So far I can only think of this:

select U.Name, R.Name
from User2Role UR join User U on UR.UserId = U.Id
                  join Role R on UR.RoleId = R.Id
where R.Name = 'A'

Adding several EXISTS which will turn it into an ugly mess. But is there a more elegant solution?

Upvotes: 0

Views: 79

Answers (2)

Donat
Donat

Reputation: 4833

You can try something like this:

with role_abc as (
    select 
        u.id, u.name,
        max(decode(r.name, 'A', 1, 0)) as has_a,
        max(decode(r.name, 'B', 1, 0)) as has_b,
        max(decode(r.name, 'C', 1, 0)) as has_c
    from usr u
         inner join user2role ur on ur.userid = u.id
         inner join role r on r.id = ur.roleid
    group by u.id, u.name
)
select id, name from role_abc
where has_a = 1 and has_b + has_c = 1

The condition is very concise and clear - at the price of the with clause.

If you need also the role names, you can easily join them.

I had to shorten the name of table user to usr because I tested on oracle.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You can use a having clause. Assuming that roles are not duplicated:

select ur.userid
from user2role ur join
     roles r
     on ur.roleid = r.id
where r.name in ('A', 'B', 'C')
group by ur.userid
having sum(case when r.name = 'A' then 1 else 0 end) > 0 and
       count(*) = 2;   -- two matches overall

A more explicit having clause would be:

having sum(case when r.name = 'A' then 1 else 0 end) = 1 and
       sum(case when r.name in ('B', 'C') then 1 else 0 end) = 1

Upvotes: 0

Related Questions