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

Felix Pamittan · Accepted Answer · 2016-05-20 01:58:35Z

2

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

answered May 20, 2016 at 1:58

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pரதீப் · Accepted Answer · 2016-05-20 01:59:00Z

1

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

answered May 20, 2016 at 1:59

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

1 Comment

hshah Over a year ago

Sweet! Cheers @Prdp :)

Bob Jarvis - Слава Україні · Accepted Answer · 2016-05-20 03:02:17Z

0

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.

edited May 20, 2016 at 3:02

answered May 20, 2016 at 2:56

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Collectives™ on Stack Overflow

Nested Oracle SQL - Multiple Values

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related