title: Multiple join with the same table count
body: <p>I have a table structure similar to below:</p>
  
  <pre><code>declare @t1 table (code varchar)
  declare @t2 table (id int, code varchar, closed bit)
  </code></pre>
  
  <p>@t2 holds open/close for the codes in @t1:</p>
  
  <pre><code>insert into @t1 values ('x')
  
  insert into @t2 values (1, 'x', 1)
  insert into @t2 values (2, 'x', 1)
  insert into @t2 values (3, 'x', 0)
  </code></pre>
  
  <p>I'm trying to get the count of open/close for a code:</p>
  
  <pre><code>select 
  count (t2_open.id) as opencount
  , count (t2_closed.id) as closecount
  from @t1 t1
  inner join @t2 t2_open on t2_open.code = t1.code AND t2_open.closed = 0
  inner join @t2 t2_closed on t2_closed.code = t1.code AND t2_closed.closed = 1
  </code></pre>
  
  <p>Which returns 2 &amp; 2, I can't see what I'm doing wrong here, why doesn't it return 1 &amp; 2?</p>
  
  <p>Shouldn't selecting * from my query return null and the correct count when the records don't match? I was expecting select * to return 3 records, but it's returning 2 records where the id is 3 in both rows.</p>