hshah hshah - 6 months ago 11
SQL Question

Nested Oracle SQL - Multiple Values

I have a table structure like:

Table = contact

Name Emailaddress ID
Bill bill@abc.com 1
James james@abc.com 2
Gill gill@abc.com 3

Table = contactrole

ContactID Role
1 11
1 12
1 13
2 11
2 12
3 12


I want to select the Name and Email address from the first table where the person has Role 12 but not 11 or 13. In this example it should return only Gill.

I believe I need a nested SELECT but having difficulty in doing this. I did the below but obviously it isn't working and returning everything.

SELECT c.Name, c.Emailaddress FROM contact c
WHERE (SELECT count(*) FROM contactrole cr
c.ID = cr.ContactID
AND cr.Role NOT IN (11, 13)
AND cr.Role IN (12)) > 0

Answer

Use conditional aggregation in Having clause to filter the records

Try this

SELECT c.NAME, 
       c.emailaddress 
FROM   contact c 
WHERE  id IN (SELECT contactid 
              FROM   contactrole 
              GROUP  BY contactid 
              HAVING Count(CASE WHEN role = 12 THEN 1 END) > 1 
                     AND Count(CASE WHEN role in (11,13) THEN 1 END) = 0)

If you have only 11,12,13 in role then use can use this

SELECT c.NAME, 
       c.emailaddress 
FROM   contact c 
WHERE  id IN (SELECT contactid 
              FROM   contactrole 
              GROUP  BY contactid 
HAVING Count(CASE WHEN role = 12 THEN 1 END) = count(*)
Comments