Why isn't this showing all rows in a left join?

I have two tables, units and unit_hits. Table units shows all available topics.

id | unit_name    
1  | unit_name1
2  | unit_name2
3  | unit_name3
4  | unit_name4

Table unit_hits shows any topics hit within a given week.

id | unit_name   | hit_count  | week
1  | unit_name1  | 1          | 3
2  | unit_name2  | 5          | 3 

I thought by doing a left join I can list all unit_names from table units and then a hit_count from unit_hits based on what week it is.

So my results would, or should, look like:

unit_name  | hit_count
un开发者_如何学Goit_name1 | 1
unit_name2 | 5
unit_name3 | 
unit_name4 | 

But unfortunately, this is not the case with the query I'm using. It is only displaying unit_name1 and unit_name2 since it has a hit count in week 3. I think it's due to the WHERE clause but I haven't been able to figure it out. Any ideas?

SELECT units.unit_name, unit_hits.unit_name, unit_hits.hit_count
FROM units LEFT JOIN [unit_hits] ON units.unit_name = unit_hits.unit_name
WHERE (((unit_hits.week)=3));

You have unit_hits.week = 3 (with lots of excessive parentheses) in your WHERE clause. That means that if no unit_hits match is found, that column is considered NULL and therefor isn't equal to 3. You can move that to the JOIN clause if that's what your actual requirement is.

This should do it. The problem is your where clause. Just add that to your join condition.

SELECT 
units.unit_name, 
unit_hits.unit_name, 
unit_hits.hit_count
FROM units 
LEFT JOIN [unit_hits] ON (units.unit_name = unit_hits.unit_name AND unit_hits.week=3)

There are no unit_hits with week = 3 for unit_name3 and unit_name 4 so they are ignored in left join.