Oracle SQL - Not in subquery

Question

I'm newish to this and using Oracle SQL. I have the following tables:

Table 1 CaseDetail

CaseNumber | CaseType
1          | 'RelevantToThisQuestion'
2          | 'RelevantToThisQuestion'
3          | 'RelevantToThisQuestion'
4          | 'NotRelevantToThisQuestion'

Table 2 LinkedPeople

CaseNumber | RelationshipType | LinkedPerson
1          | 'Owner'          | 123
1          | 'Agent'          | 124
1          | 'Contact'        | 125
2          | 'Owner'          | 126
2          | 'Agent'          | 127
2          | 'Contact'        | 128
3          | 'Owner'          | 129
3          | 'Agent'          | 130
3          | 'Contact'        | 131

Table 3 Location

LinkedPerson| Country
123         | 'AU'
124         | 'UK'
125         | 'UK'
126         | 'US'
127         | 'US'
128         | 'UK'
129         | 'UK'
130         | 'AU'
131         | 'UK'

I want to count CaseNumbers that are relevant to this question with no LinkedPeople in 'AU'. So the results from the above data would be 1

I've been trying to combine aggregate functions and subqueries but I think I might be over-complicating things.

Just need a push in the right direction, thanks!

Sorry I was trying to be clever by simplifying my question scenario but actually I over-simplified and made the question completely different. — user4946727
– user4946727, Commented May 27, 2015 at 22:54

JustinHui · Accepted Answer · 2015-05-27 23:01:03Z

1

To get all the records:

SELECT COUNT(DISTINCT CaseNumber) 
FROM LinkedPeople
WHERE CaseNumber NOT IN 
(
  SELECT DISTINCT C.CaseNumber
  FROM CaseDetail C       
  INNER JOIN LinkedPeople P ON C.CaseNumber = P.CaseNumber
  INNER JOIN Location L
  ON P.LinkedPerson = L.LinkedPerson
  WHERE Country = 'AU' AND C.CaseType = 'RelevantToThisQuestion'
)

edited May 27, 2015 at 23:01

answered May 27, 2015 at 22:53

JustinHui

7495 silver badges18 bronze badges

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

1 Comment

user4946727 Over a year ago

Thanks this is what I was looking for. Only change I made is to reverse the C.CaseType criteria in the subquery.

MT0 · Accepted Answer · 2015-05-27 23:36:48Z

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE CASEDETAIL ( CaseNumber, CaseType ) AS
          SELECT 1, 'RelevantToThisQuestion' FROM DUAL
UNION ALL SELECT 2, 'RelevantToThisQuestion' FROM DUAL
UNION ALL SELECT 3, 'RelevantToThisQuestion' FROM DUAL
UNION ALL SELECT 4, 'NotRelevantToThisQuestion' FROM DUAL;

CREATE TABLE LINKEDPEOPLE ( CaseNumber, RelationshipType, LinkedPerson ) AS
          SELECT 1, 'Owner', 123 FROM DUAL
UNION ALL SELECT 1, 'Agent', 124 FROM DUAL
UNION ALL SELECT 1, 'Contact', 125 FROM DUAL
UNION ALL SELECT 2, 'Owner', 126 FROM DUAL
UNION ALL SELECT 2, 'Agent', 127 FROM DUAL
UNION ALL SELECT 2, 'Contact', 128 FROM DUAL
UNION ALL SELECT 3, 'Owner', 129 FROM DUAL
UNION ALL SELECT 3, 'Agent', 130 FROM DUAL
UNION ALL SELECT 3, 'Contact', 131 FROM DUAL;

CREATE TABLE LOCATION ( LinkedPerson, Country ) AS
          SELECT 123, 'AU' FROM DUAL
UNION ALL SELECT 124, 'UK' FROM DUAL
UNION ALL SELECT 125, 'UK' FROM DUAL
UNION ALL SELECT 126, 'US' FROM DUAL
UNION ALL SELECT 127, 'US' FROM DUAL
UNION ALL SELECT 128, 'UK' FROM DUAL
UNION ALL SELECT 129, 'UK' FROM DUAL
UNION ALL SELECT 130, 'AU' FROM DUAL
UNION ALL SELECT 131, 'UK' FROM DUAL;

Query 1:

SELECT COUNT( DISTINCT CASENUMBER ) AS Num_Relevant_Cases
FROM   CASEDETAIL c
WHERE  CaseType = 'RelevantToThisQuestion'
AND    NOT EXISTS ( SELECT 1
                    FROM   LINKEDPEOPLE p
                           INNER JOIN LOCATION l
                           ON ( p.LinkedPerson = l.LinkedPerson )
                    WHERE  c.CaseNumber = p.CaseNumber
                    AND    l.Country = 'AU' )

Results:

| NUM_RELEVANT_CASES |
|--------------------|
|                  1 |

user985366 · Accepted Answer · 2015-05-27 22:46:11Z

0

I'm not sure about the exact syntax, but I believe you want something like:

select count distinct CaseNumber from LinkedPeople where Country != 'AU'

answered May 27, 2015 at 22:46

user985366

1,7043 gold badges19 silver badges43 bronze badges

1 Comment

user4946727 Over a year ago

Sorry, I was trying to be clever in my question, there's actually a third table with the country information. I'll edit.

Collectives™ on Stack Overflow

Oracle SQL - Not in subquery

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related