Left Outer Join does not return all rows from my left table?

Question

I am trying to get the number of pages per day using the following query.

SELECT day.days, COUNT(*) as opens FROM day LEFT OUTER JOIN tracking ON day.days = DAY(FROM_UNIXTIME(open_date)) WHERE tracking.open_id = 10 GROUP BY day.days 

The output I get is this:

 days opens 1 9 9 2 

The thing is, in my daily table, I have one column that contains a number from 1 to 30 to represent days per month. I made a left outer join, and I expect to show all the days in the days column!

But my request does this, why can it be?

Thanks to everyone for any help.

Answer 1

You indicate that the included tracking.open_id file should be 10. For the rest of the lines, this will be NULL, so they will not be displayed!

Answer 2

Nanne's answer explains why you are not getting the desired result (the WHERE clause removes the rows), but not how to fix it.

The solution is to change WHERE to AND, so the condition is part of the join condition, not the filter applied after the join:

 SELECT day.days, COUNT(*) as opens FROM day LEFT OUTER JOIN tracking ON day.days = DAY(FROM_UNIXTIME(open_date)) AND tracking.open_id = 10 GROUP BY day.days 

Now all the results in the left table will be present in the results.