CODEWITHSUNDEEP
sqloracle-database
user3553846
user3553846

Reputation: 342

SELECT clause using not exists condition

Five tables:

//Applicant
A#
-----------------
1
2
3
4
5

//coursepass
A#         ctitle
-------------------
1          hello
2          testing
3          yeah
4          coding
5          computer

//position
p#
----------------
0001
0002
0003
0004
0005
0006

//applies
A#         p#
-------------
1          0001
2          0001
3          0002
4          0003
5          0004
6          0005

//Sneeded
p#         sname
-----------------
0001        Java
0002         C++
0003         C#
0004         C
0005         C

My question is to find the titles of coursepassed by the applicant who did not apply any position that need a skill name ='C'

So for my table above. we can see that p# 0004,0005 need 'C', so the A#=1,2,3,4 apply an position but the position doesn't need 'C' so refer back to the coursepass, the ctitle hello, testing, yeah, coding will be display

//RESULT
CTITLE
-------------
hello
testing
yeah
coding

I use this query, but cannot get the result I want

SELECT cp.CTITLE
  FROM COURSEPASSED cp
 WHERE NOT EXISTS (SELECT a.A#
                     FROM APPLIES a
                     JOIN SNEEDED sn ON a.P#=sn.P# and sn.SNAME='C');

The following query returns the result I expect.

SELECT cp.CTITLE 
  FROM COURSEPASSED cp
 WHERE cp.A# NOT IN (SELECT a.A#
                       FROM APPLIES a
                       JOIN SNEEDED sn ON a.P#=sn.P# and sn.SNAME='C');

But I want to use NOT EXISTS condition. How should I change my query?

Upvotes: 0

Views: 61

Answers (1)

D Stanley
D Stanley

Reputation: 152566

Your NOT EXISTS clause should reference some value from the outer table. 

SELECT cp.CTITLE FROM COURSEPASS cp
WHERE NOT EXISTS
(
    SELECT a.A# FROM APPLIES a JOIN SNEEDED sn 
    ON a.P#=sn.P# and sn.SNAME='C'
    WHERE a.A# = cp.A#    // notice reference back to cp
);

Upvotes: 2

Related Questions