Nested Oracle SQL - Multiple Values

Question

I have a table structure like:

Table = contact

Name    Emailaddress    ID
Bill    [email protected]    1
James   [email protected]   2
Gill    [email protected]    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 1

You can use a combination of EXISTS and NOT EXISTS

SELECT *
FROM contact c
WHERE
    EXISTS(SELECT 1 FROM contactrole cr WHERE cr.ContactID = c.ID AND cr.Role = 12)
    AND NOT EXISTS(SELECT 1 FROM contactrole cr WHERE cr.ContactID = c.ID AND cr.Role IN(11, 13))

---

Another option is to use GROUP BY and HAVING:

SELECT c.*
FROM contact c
INNER JOIN contactrole cr
    ON cr.ContactID = c.ID
GROUP BY
    c.ID, c.Name, c.Emailaddress
HAVING 
    SUM(CASE WHEN cr.Role = 12 THEN 1 ELSE 0 END) > 0
    AND  SUM(CASE WHEN cr.Role IN(11, 13) THEN 1 ELSE 0 END) = 0

Answer 2

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(*)

Answer 3

You can do this using JOINs:

SELECT c.*
  FROM CONTACT c
  INNER JOIN CONTACTROLE cr12
    ON cr12.CONTACTID = c.ID AND
       cr12.ROLE = 12
  LEFT OUTER JOIN CONTACTROLE cr11
    ON cr11.CONTACTID = c.ID AND
       cr11.ROLE = 11
  LEFT OUTER JOIN CONTRACTROLE cr13
    ON cr13.CONTACTID = c.ID AND
       cr13.ROLE = 13
  WHERE cr11.ROLE IS NULL AND
        cr13.ROLE IS NULL

The INNER JOIN CONTACTROLE cr12 requires that role 12 exist for the given contact ID; the LEFT OUTER JOIN CONTACTROLE cr11 and LEFT OUTER JOIN CONTRACTROLE cr13 check to see if roles 11 and 13 might exist for the given contact ID; and the WHERE clause verifies that neither roles 11 or 13 exist.

Best of luck.