Not able to write correct mysql join query

Question

I have following 2 tables in mysql

1. Table-Name a

id int auto-increment PK
name varchar not null
year int(4) not null

2. Table-Name b
id int auto-increment PK
term varchar not null
a_id int FK references a.id
year int(4) not null

1. data is as follow

select * from a;

1,'Ravi',2010
2,'Kumar',2011

select * from b;

1,'a',1,2009
2,'b',1,2010
3,'c',1,2008
4,'d',2,2008
5,'e',2,2009
6,'f',2,2010

Now i wrote a query for result set that it should return a.id and count(b.id) if b table has a record with a.id and a.year=b.year

eg -

id | cnt
------------
1  | 1
2  | 0
------------

Here is my query -

select a.id,count(b.id) cnt from a
left join b
on b.a_id=a.id
where a.year=b.year
group by id;

which returns resultset -

id | cnt
------------
1  | 1

so behaviour is pretty obvious to me but i am not able to write query to get the resultset as i said earlier.

Answer 1

Your WHERE clause essentially converts LEFT JOIN to an INNER JOIN. You should move the predicate of WHERE to ON:

select a.id,count(b.id) cnt from a
left join b
on b.a_id=a.id AND a.year=b.year
group by id;

This way you get all a rows returned. If no match is found then cnt is going to be 0.