CODEWITHSUNDEEP
sql-servert-sql
user8232137
user8232137

Reputation: 13

Select query with only a single record in a mapping table

Please, can someone help me with what is possibly a simple query?

We have two tables with below structure.

Customer table:

+----+-----------+
| id |   name    |
+----+-----------+
|  1 | customer1 |
|  2 | customer2 |
|  3 | customer3 |
+----+-----------+

Customer role mapping table:

+-------------+-----------------+
| customer_id | customerRole_id |
+-------------+-----------------+
|           1 |               1 |
|           1 |               2 |
|           2 |               1 |
|           3 |               1 |
|           4 |               1 |
|           5 |               1 |
+-------------+-----------------+

I want to select customers with role id 1 only NOT with role id 1 AND 2.

So, in this case, it would be customer id 2,3,4 & 5. ignoring 1 as it has multiple roles.

Is there a simple query to do this?

Many thanks, for any help offered.

Upvotes: 1

Views: 84

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

Hmmm, there are several ways to do this. 

select c.*
from customers c
where exists (select 1 from mapping m where m.customerid = c.id and m.role = 1) and
      not exists (select 1 from mapping m where m.customerid = c.id and m.role <> 1);

If you just want the customer id, a perhaps simpler version is:

select customerid
from mapping
group by customerid
having min(role) = 1 and max(role) = 1;

This solution assumes that role is never NULL.

Upvotes: 2

Related Questions