select ca.categname, sc.subcategname
,(select  count(*) from alldata a where a.categid=ca.categid and a.subcategid=sc.subcategid and a.year= 2009  ) as "2009"
,(select  count(*) from alldata a where a.categid=ca.categid and a.subcategid=sc.subcategid and a.year= 2010  ) as "2010"
from category_v1 ca
left join subcategory_v1 sc on sc.categid=ca.categid
order by  1 , 2 

select category, subcategory, count(*) from alldata a 
where year= 2010 
group by categid, subcategid
order by  1 , 2 

select c.categname, d.subcategname, ifnull (b.counter, 0 ) as "2010", ifnull (a.counter, 0 ) as "2009"  from category_v1 c
left join subcategory_v1 d on c.categid=d.categid
--2010
left join (select categid, subcategid, count(*) counter from alldata a  where year= 2010  
group by categid, subcategid) b on c.categid=b.categid and b.subcategid=d.subcategid
--2009
left join (select categid, subcategid, count(*) counter from alldata a 
where year= 2009  group by categid, subcategid) a on c.categid=a.categid and a.subcategid=d.subcategid
order by  1 , 2