SQL - EXISTS/NOT EXISTS function

Question

I'm trying to learn SQL and I'm having a little trouble here.

 ID  |  P_Id |  room | 
======================
 1   |   8   |   A   |
 2   |   8   |   A   |
 3   |   8   |   B   |
 4   |   9   |   B   |
 5   |   9   |   B   |
 6   |   10  |   C   |
 7   |   10  |   C   |
 8   |   10  |   D   |

I'm trying to figure out which P_Id has only worked in room B. So the outcome would be P_Id = 9. Not 8 as well, because he worked in room A as well.

This is my query, but it doesn't work:

SELECT Room.P_Id
FROM Room
WHERE NOT EXISTS (SELECT *
                  FROM Room
                  WHERE Room.room <> 'B');

Could you guys help me out here?

jpw · Accepted Answer · 2016-04-16 10:00:19Z

2

You need to correlate the subquery with the outer query and use different aliases in the outer and inner queries, or the subquery will use the wrong table. The correlation makes sure the subquery applies to the correct row in the outer query (where the P_Id matches).

Do this instead:

SELECT DISTINCT r1.P_Id
FROM Room r1
WHERE NOT EXISTS (
  SELECT *
  FROM Room r2
  WHERE r2.room <> 'B'
    AND r1.P_Id = r2.P_Id
);

edited Apr 16, 2016 at 10:00

answered Apr 16, 2016 at 9:55

jpw

45k6 gold badges71 silver badges90 bronze badges

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

Comments

marc_s · Accepted Answer · 2016-04-16 11:05:10Z

0

Try this

Select distinct p_ID 
from room 
where room = 'B' 
  and p_id not in (Select p_ID from room where room <> 'B')

edited Apr 16, 2016 at 11:05

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Apr 16, 2016 at 9:57

Akshey Bhat

8,6872 gold badges23 silver badges20 bronze badges

Comments

David Laplante · Accepted Answer · 2016-04-16 16:14:10Z

0

How about this:

Select Room.P_Id
FROM Room
WHERE MIN(room.room) = MAX(room.room) and MIN(room.room) = 'B'
Group by Room.P_Id

There might be other more elegant ways, but this will get the job done.

edited Apr 16, 2016 at 16:14

answered Apr 16, 2016 at 9:56

David Laplante

1091 silver badge12 bronze badges

Comments

Asif Patankar · Accepted Answer · 2018-08-26 13:02:25Z

0

You can also try

SELECT DISTINCT P_Id FROM Room WHERE NOT EXISTS (SELECT P_Id FROM Room WHERE room <>'B');

edited Aug 26, 2018 at 13:02

answered Apr 16, 2016 at 10:10

Asif Patankar

954 silver badges15 bronze badges

Collectives™ on Stack Overflow

SQL - EXISTS/NOT EXISTS function

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related