Left join not working - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Left join not working

Author

Topic

michaelwhaley
Starting Member

5 Posts

Posted - 2008-08-01 : 11:41:46

This query is not working like a normal left join. If there is no corresponding timecard info for a phase/wcno then no result is returned for that phase/wcno, when I would like to still get the budget info.

select b.wcno, b.phaseno, b.Bud_Hrs, ISNULL(t.TC_Hrs,0) AS TC_Hrs, ISNULL(t.TC_Cost,0) AS TC_Cost
from
(
select PhaseNo, WCNo, sum(Hrs) as Bud_Hrs
from Budget
where co=@Co
and jobno=@JobNo
group by PhaseNo, WCNo
) b
left join
(
select t.PhaseNo, t.WCNo, sum(t.RegHrs+t.OTHrs+t.DTHrs) as TC_Hrs
, sum(reghrs*regrate+othrs*otrate+dthrs*dtrate+rrhrs*regrate+orhrs*otrate+drhrs*dtrate + FICA+ FUTA+ Medicare+ SUI+ ETT+ Fringe+ WrkComp) as TC_Cost
from Timecards t
left join JobPhases jp on jp.co=t.co and jp.jobno=t.jobno and jp.phaseno=t.phaseno
left join workcodes wc on wc.wcno=t.wcno
where t.co=@Co
and t.jobno=@JobNo
and t.worktype='contract'
and jp.restricted='n'
and wc.restricted='n'
group by t.PhaseNo, t.WCNo
) t on t.PhaseNo = b.PhaseNo and t.wcno=b.wcno
order by t.wcno, t.phaseno

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-01 : 11:44:15

[code]
left join JobPhases jp on jp.co=t.co and jp.jobno=t.jobno and jp.phaseno=t.phaseno and jp.restricted = 'n'
left join workcodes wc on wc.wcno=t.wcno and wc.restricted='n'
where t.co = @Co
and t.jobno = @JobNo
and t.worktype = 'contract'
~~and jp.restricted = 'n'~~
~~and wc.restricted='n'~~
[/code]

KH
[spoiler]Time is always against us[/spoiler]