SELECT clause using not exists condition

Question

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?

i just typo . but it's still fail to get the result

user3553846
– user3553846

2014-05-15 16:17:07 +00:00
Commented May 15, 2014 at 16:17 — user3553846
– user3553846, Commented May 15, 2014 at 16:17

D Stanley · Accepted Answer · 2014-05-15 16:26:03Z

2

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

answered May 15, 2014 at 16:26

D Stanley

153k12 gold badges190 silver badges257 bronze badges

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

2 Comments

user3553846 Over a year ago

after your explanation, i able to do it already ! THANKS YOU !

user3553846 Over a year ago

by the way can you help me to see this thread? stackoverflow.com/questions/23674644/…

Collectives™ on Stack Overflow

SELECT clause using not exists condition

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related