SQL Left Join losing rows after filtering

SQL Left Join losing rows after filtering

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.
Table1
Label | FK
Blue | 1
Red | 2
Green | 3
Table2
Values Color Date
Dog | 1 | 02/02/2010
Cat | 2 | 02/02/2010
Dog | 1 | 02/02/2010
Cat | 2 | 02/02/2010
SELECT 1.Label, COUNT(2.values)
FROM Table1 1
LEFT JOIN Table2 2 ON 1.fk = 1.pk
GROUP BY 1.Label
Good Result Set - No filters
Blue | 2
Red | 2
Green | 0
Great!
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.
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
Bummer Result Set - After Filters
Blue | 2
Red | 2
Dukes!
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!
Do a second join or recursive join. Get my "good" join table, get a second
"filtered" table, then LEFT JOIN them