CODEWITHSUNDEEP
sqlpostgresql
DragonMasa
DragonMasa

Reputation: 63

How do I return the branches which have no manager? (SQL)

I am trying to return the branches which have no manager.

Here are the tables

And this is what I tried:

SELECT branchno, city FROM branch
WHERE branchno IN
(SELECT branchno FROM STAFF
WHERE position <> 'Manager');

And it returns:

 branchno |   city
----------+----------
 B005     | London
 B007     | Aberdeen
 B003     | Glasgow
 (3 rows)

Upvotes: 0

Views: 129

Answers (2)

Rahul
Rahul

Reputation: 77896

There are many ways, you can use WHERE NOT EXISTS like below. BTW, your posted query should as well give you the desired result (per your post title). What else you are looking for?

SELECT b.branchno, b.city 
FROM branch b
WHERE NOT EXISTS 
(SELECT 1 FROM STAFF WHERE branchno = b.branchno
AND position = 'Manager');

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

You can easily do this using not in

select *
from branch
where branchno not in (
  select branchno from staff
  where position = 'Manager'
);

Upvotes: 1

Related Questions