How do I replace NOT EXISTS with JOIN?

Question

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

Answer 1

Inner join with the name you want, left join on the name you dont, then use where to ensure the left join fails to match, like so (SQL Fiddle):

select distinct a.id, a.name
from Employee a
  inner join Dependencies b on a.id = b.eid
    and b.name = 'Orange'
  left join Dependencies c on ( a.id = c.eid
    and c.name = 'Apple')
where c.id is null;