mysql>select categoryname,count(songname) from songcat,song where songcat.id=song.id group by categoryname;
try it: select categoryname ,when b.sl is null then 0 else b.sl end from songcat a left outer join ( select id ,cid ,count(*) as sl from song group by id ,cid ) b on a.id = b.id
select categoryname ,when b.sl is null then 0 else b.sl end from songcat a
left outer join
(
select id ,cid ,count(*) as sl from song group by id ,cid
) b
on a.id = b.id