get multiple zero count !

[mounika]

HI,

Can any one tell what should be the query to count from two tables where we have to get zero count for null.

Ex:
[b]Table 1:                  Table 2:[/b]
[b]ID Name                  ID  Subjects  Table1ID [/b]   
1  abc                        1    English    1
2  xyz                        2    Maths      1
3  mno                      3    Science    1

Result should be:

[b]Name  count(subjects)[/b]
abc      3
xyz      0
mno      0

Thanks in Advance!

[fenway]

Try the following (UNTESTED):

[code]
SELECT t1.name, COUNT(t2.id)
FROM t1
LEFT JOIN t2 ON ( t2.table1id = t1.id )
GROUP BY t1.id
[/code]