How do I replace NOT EXISTS with JOIN?
- by YelizavetaYR
I've got the following query:
select distinct a.id, a.name
from Employee a
join Dependencies b on a.id = b.eid
where not exists
(
select *
from Dependencies d
where b.id = d.id
and d.name = 'Apple'
)
and exists
(
select *
from Dependencies c
where b.id = c.id
and c.name = 'Orange'
);
I have two tables, relatively simple.
The first Employee has an id column and a name column
The second table Dependencies has 3 column, an id, an eid (employee id to link) and names (apple, orange etc).
the data looks like this
Employee table looks like this
id | name
-----------
1 | Pat
2 | Tom
3 | Rob
4 | Sam
Dependencies
id | eid | Name
--------------------
1 | 1 | Orange
2 | 1 | Apple
3 | 2 | Strawberry
4 | 2 | Apple
5 | 3 | Orange
6 | 3 | Banana
As you can see Pat has both Orange and Apple and he needs to be excluded and it has to be via joins and i can't seem to get it to work. Ultimately the data should only return Rob
