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.
| Author |
Topic |
|
Cyclonik
Posting Yak Master
114 Posts |
 Posted - 2001-04-05 : 16:38:23
|
| i have some left joins that aren't working properly.the following is the querySELECT b.Fname,b.Lname,b.lanid, Avg(CASE c.criteria_id WHEN 1 THEN POINTS END) AS CE1, Avg(CASE c.criteria_id WHEN 2 THEN points END) AS CE2, Avg(CASE c.criteria_id WHEN 3 THEN points END) AS CE3, Avg(CASE c.criteria_id WHEN 4 THEN points END) AS CE4, Avg(CASE c.criteria_id WHEN 5 THEN points END) AS CE5, Avg(CASE c.criteria_id WHEN 6 THEN points END) AS CE6, Avg(CASE c.criteria_id WHEN 7 THEN points END) AS CE7, Avg(CASE c.criteria_id WHEN 8 THEN points END) AS AE1, AE2=IsNull(Avg(CASE c.criteria_id WHEN 9 THEN points END),-1), AE3 = IsNULL(Avg(CASE c.criteria_id WHEN 10 THEN points END),-1), Avg(CASE c.criteria_id WHEN 11 THEN points END) AS AE4, TS1=IsNUll(Avg(CASE c.criteria_id WHEN 12 THEN points END),0), OS1=IsNull(Avg(CASE c.criteria_id WHEN 13 THEN points END),-1), Avg(CASE c.criteria_id WHEN 14 THEN points END) AS OS2, Count(distinct a.monitor_id) as TotalMonitors From tblUsersT b LEFT JOIN tblVoice_MonitorsT a ON b.lanid=a.lanid JOIN tblVoice_monitor_scores_idx c ON a.monitor_id=c.monitor_idWHERE b.managerid = 'bowens' AND (a.monitor_date >= '03/01/01' AND a.monitor_date <= '03/31/01' OR a.monitor_date Is Null)GROUP BY b.Lname,b.Fname,b.lanidnow what happens is that if a joining record does not exist in teh tblVoiceMOnitorsT table the record doesn't show up in the list.when all the records from the tblUsersT table passing the where condition should be returned or so i think. SQL server 6.5 btw in case that might matter.ThanksChristian-=:SpasmatiK:=- |
|
|
|
|
|
|
|
Incorrect Query results...