Count from two table in one query.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count from two table in one query.

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-11-21 : 01:40:55

Dear All,

I am using folowing query for taking count from two table and getting following output. But when data is not availbale in "tb_lcr_normalized_rate_sheet_BK" table then it should show 0 just like Desired Output. But through this query if record is not available in backup table then it's not return any output.

Can anybody please solve my issue ?

Query:
select rs.ID,COUNT(nr.ID) As NormalizedCountOriginal,COUNT(nrs.ID) As NormalizedCountBackup from tb_lcr_rate_sheet rs
inner join tb_lcr_work_sheet ws on rs.ID = ws.Rate_Sheet_ID
inner join tb_lcr_normalized_rate_sheet nr on ws.ID = nr.Work_Sheet_ID
inner join tb_lcr_normalized_rate_sheet_BK nrs on nr.ID = nrs.ID
where rs.status = 0
group by rs.ID

Output:
ID tb_lcr_normalized_rate_sheet tb_lcr_normalized_rate_sheet_BK
123 70 70

Desired Output:
ID tb_lcr_normalized_rate_sheet tb_lcr_normalized_rate_sheet_BK
123 70 0

Thanks and Regard's
Harish Patil

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-21 : 02:33:59

Change last inner join into Left join

Madhivanan

Failing to plan is Planning to fail