title: How do I replace NOT EXISTS with JOIN?
body: <p>I've got the following query:</p>
  
  <pre><code>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'
      );
  </code></pre>
  
  <p>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). </p>
  
  <p>the data looks like this
  Employee table looks like this</p>
  
  <pre><code>id  | name
  -----------
  1   | Pat
  2   | Tom
  3   | Rob
  4   | Sam
  </code></pre>
  
  <p>Dependencies </p>
  
  <pre><code>id  | eid | Name
  --------------------
  1   | 1   |  Orange
  2   | 1   |  Apple
  3   | 2   |  Strawberry
  4   | 2   |  Apple
  5   | 3   |  Orange
  6   | 3   |  Banana
  </code></pre>
  
  <p>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</p>