title: Sql multiple joins to same table both inclusive and exclusive
body: <p>I have a table Foo</p>
  
  <pre><code>FOO
  -------
  id
  name
  
  BAR
  -------
  id
  name
  
  FOO_BAR_XREF
  -------
  foo_id
  bar_id
  </code></pre>
  
  <p>I need to select all instances of FOO where foo.id has a record in foo_bar_xref with a bar_id of 1 and exclude records from foo where foo.id has a record n foo_bar_xref with a bar_id of 2</p>
  
  <p>foo -> foo_bar_xref is one to many *emphasized text*foo_bar_xref may contain multiple bar_id's per foo_id</p>
  
  <p>Is this possible with only joins or do I need to use not exists statements in the where clause?</p>
  
  <p>so far I have </p>
  
  <pre><code>select f.name from FOO f
  inner join FOO_BAR_XREF fb_1 on fb_1.foo_id = f.id
  inner join FOO_BAR_XREF fb_2 on fb_2.foo_id = f.id
  
  where fb_1.bar_id = 1 and fb_2.bar_id &lt;&gt; 2
  group by f.name -- remove dupes - running on sql server and is paged with sql servers hackish over keyword where distinct doesn't work so well.
  </code></pre>
  
  <p>thats not filtering out the foo's that have a bar of 2</p>
  
  <p>THIS seems to work but I'm not sure it's the most efficient</p>
  
  <pre><code>select f.name from FOO f
  inner join FOO_BAR_XREF fb_1 on fb_1.foo_id = f.id and fb_1.bar_id = 1
  left outer join FOO_BAR_XREF fb_2 on fb_2.foo_id = f.id and fb_2.bar_id = 2
  
  where fb_2.bar_id is null
  group by f.name -- remove dupes
  </code></pre>