title: SQL Left Join losing rows after filtering
body: <p>I have a multi-table join (only two shown in example) where I need to retain all rows from the base table.  Obviously I use a LEFT JOIN to include all rows on the base table. Without the WHERE clause it works great – When a row doesn’t exist in the Right table the row from the Left table still shows, just with a 0 from the column in the Right table.  The first two rows in the dataset are Labels from the Left table and Count of rows from the Right table, grouped by Label.   All I want is a count of 0 when a label does not have a value from Table2 assigned.</p>
  <p><strong>Table1</strong></p>
  <pre><code>Label | FK
  ----------
  Blue  | 1
  Red   | 2
  Green | 3
  </code></pre>
  <p><strong>Table2</strong></p>
  <pre><code>Values | pk    | Date
  ---------------------------
  Dog    | 1     | 02/02/2010
  Cat    | 2     | 02/02/2010
  Dog    | 1     | 02/02/2010
  Cat    | 2     | 02/02/2010
  </code></pre>
  <p><strong>Query</strong>:</p>
  <pre><code>SELECT 1.Label, COUNT(2.values)
  FROM Table1 1
      LEFT JOIN Table2 2 ON 1.fk = 2.pk
  GROUP BY 1.Label
  </code></pre>
  <p>Good Result Set - No filters</p>
  <pre><code>Blue  | 2
  Red   | 2
  Green | 0
  </code></pre>
  <p>Great!</p>
  <p>My issue is that when I add filtering criteria to remove rows from the Right table the row is removed for my Left join rows (zeroing them out), the Left rows are dropped.  I need the Left rows to remain even if their count is filtered down to zero.</p>
  <pre><code>SELECT 1.Label, COUNT(2.values)
  FROM Table1 1
      LEFT JOIN Table2 2 ON 1.fk = 1.pk
  WHERE 2.Date BETWEEN '1/1/2010' AND '12/31/2010'    
  GROUP BY 1.Label
  </code></pre>
  <p>Bummer Result Set - After Filters</p>
  <pre><code>Blue | 2
  Red  | 2
  </code></pre>
  <p>Dukes!</p>
  <p>So, what the hell? Do I need to get a temp table with the filtered dataset THEN join it to the Left table? What am I missing?
  Thanks!</p>
  <p>Do a second join or recursive join.  Get my “good” join table, get a second “filtered” table, then LEFT JOIN them</p>