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 |
|
swatib
Posting Yak Master
173 Posts |
 Posted - 2006-01-24 : 06:10:52
|
For a report, I'm writing a query. I'm retrieving data from more than one child views. All the child views have common ID field- AgentID.The query is like this-SELECT L.AgentID, LoggedInTime, NotReadyTime, RingTime, HoldTime, IdleTime, CallsPresented, CallsAnswered, CallsConference, CallsTransferedFROM Vw_LoggedInTime L LEFT OUTER JOIN Vw_NotReadyTime N ON L.AgentID=N.AgentID LEFT OUTER JOIN Vw_RingTime R ON N.AgentID=R.AgentIDLEFT OUTER JOIN Vw_HoldTime H ON R.AgentID=H.AgentIDLEFT OUTER JOIN Vw_IdleTime I ON H.AgentID=I.AgentIDLEFT OUTER JOIN Vw_CallAnswered A ON I.AgentID=A.AgentIDLEFT OUTER JOIN Vw_CallConference C ON A.AgentID=C.AgentIDLEFT OUTER JOIN Vw_CallTransfered T ON C.AgentID=T.AgentIDI want data for all agents where the child views may or may not have the data. The above query is working but since the view Vw_RingTime can't have the record for a agent, the data from Vw_IdleTime is not displaying in the result.Plz give me proper way to the solution.Njoy Life  |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-01-24 : 06:24:36
|
join it to another table...so what do you join Vw_RingTime on if it doesn't have agentID?Go with the flow & have fun! Else fight the flow  |
 |
|
|
swatib
Posting Yak Master
173 Posts |
Posted - 2006-01-24 : 07:23:26
|
| Vw_RingTime has no record for an agent but Vw_IdleTime has the record for that agent but because of Vw_RingTime join specified before the Vw_IdleTime, the records are not displayed properly.Njoy Life |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2006-01-24 : 07:38:59
|
| Try FULL OUTER JOIN instead of LEFT OUTER JOINDuane. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-01-24 : 08:53:52
|
| Join to the AgentId in your primary view listed in your FROM clause. And be sure that the view in your FROM clause is truly the one that you want to drive your results. i.e.,FROM Vw_LoggedInTime L...LEFT OUTER JOIN Vw_RingTime R ON N.AgentID=L.AgentIDThough this SELECT looks very suspect. unless all of those views returns 1 row per agentID, it looks like you will get lots of cross joins and duplicates. |
|
|
|
swatib
Posting Yak Master
173 Posts |
Posted - 2006-01-24 : 23:58:48
|
quote:
Originally posted by jsmith8858
Join to the AgentId in your primary view listed in your FROM clause. And be sure that the view in your FROM clause is truly the one that you want to drive your results. i.e.,FROM Vw_LoggedInTime L...LEFT OUTER JOIN Vw_RingTime R ON N.AgentID=L.AgentID
Thanks a lot, actually I'm using Outer join first time....I'll keep this in mind when using Left Outer Join.Thanks Jsmith8858Njoy Life |
|
|
|
|
|
|
|
|
Left Outer Join