CODEWITHSUNDEEP
sqloracle-database
user4946727
user4946727

Reputation: 23

Oracle SQL - Not in subquery

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!

Upvotes: 2

Views: 243

Answers (3)

MT0
MT0

Reputation: 167877

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 |

Upvotes: 1

JustinHui
JustinHui

Reputation: 729

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'
)

Upvotes: 1

user985366
user985366

Reputation: 1699

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

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

Upvotes: 0

Related Questions