How to join tables with 0 count rows.
Posted on 2012-04-02
I have two tables.
1.geotable - It contains source,state and district.
There are total 48 districts. This is master table.
2.mstchvs - Here data entry is done and source,state district fields are there for every records.
I want the following summary
State,District,source,Total_count as 'No. of enteries'
The query should display 0 for those records which doesnot exists for a given condition of dates.
left join mstchvs
ON geotable.state=mstchvs.state and geotable.district=mstchvs.district and geotable.source=mstchvs.source
where flagr=1 and rfeeddate>='2012-1-1' and rfeeddate<='2012-3-31'
group by geotable.state,geotable.district,geotable.source
order by 1 asc,2 asc ,3 asc
This query return only those rows where record exists.