SQL Left Join losing rows after filtering

left-joinsql

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 | pk    | Date
---------------------------
Dog    | 1     | 02/02/2010
Cat    | 2     | 02/02/2010
Dog    | 1     | 02/02/2010
Cat    | 2     | 02/02/2010

Query:

SELECT 1.Label, COUNT(2.values)
FROM Table1 1
    LEFT JOIN Table2 2 ON 1.fk = 2.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

Best Answer

You are filtering on the second table in the where. The values could be NULL and NULL fails the comparisons.

Move the where condition to the on clause:

SELECT 1.Label, COUNT(2.values)
FROM Table1 1 LEFT JOIN
     Table2 2
     ON 1.fk = 1.pk AND
        2.Date BETWEEN 1/1/2010 AND 12/31/2010    
GROUP BY 1.Label

Note:

The date formats retain the dates from the question. However, I don't advocate using BETWEEN for dates and the conditions should use standard date formats:

SELECT 1.Label, COUNT(2.values)
FROM Table1 1 LEFT JOIN
     Table2 2
     ON 1.fk = 1.pk AND
        2.Date >= '2010-01-01' AND
        2.Date < '2011-01-01'
GROUP BY 1.Label;

Some databases support the SQL Standard keyword DATE to identify date constants.

Related Topic