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 |
|
benjamintb
Starting Member
15 Posts |
 Posted - 2004-06-25 : 03:01:54
|
| hi,having some issues. I need to join four tables(I`m using select statements to create the "tables"). Each table has but one row and another row that always contains a field called 'whatever' with the value 1. My plan was to join each of the 4 tables on this 'whatever' field so the result would end up looking like i had just clumbed the four tables side by side and made a new table. As there are more rows in some than others i was hoping there would be some nulls to fill in the blanks. Is there a way to do this? I`ve started just by trying to join 2 tables but couldn`t get that to work. here`s some of the sql so far:SELECT DISTINCT Response_in.Call_Id AS R_IN, Response_out.Call_Id AS R_OUT, Response_in.whateverFROM (SELECT DISTINCT dbo.Call.Call_Id, dbo.Call.Open_Dt, '1' AS whatever, dbo.Company.Company_Name FROM dbo.Call INNER JOIN dbo.Call_Action ON dbo.Call.Call_Id = dbo.Call_Action.Call_Id INNER JOIN dbo.Response_Time ON dbo.Call.Priority_Id = dbo.Response_Time.Priority_Id AND dbo.Call_Action.Action_Dt < DATEADD([hour], dbo.Response_Time.Response_Time, dbo.Call.Open_Dt) INNER JOIN dbo.Project ON dbo.Response_Time.SLA_Id = dbo.Project.SLA_Id INNER JOIN dbo.Company ON dbo.Project.Company_Id = dbo.Company.Company_Id WHERE (dbo.Call_Action.Action_Notes LIKE N'%changed the status of this Call from ''Pickup'' to%') AND (dbo.Call.Open_Dt > '06-20-2004')) AS Response_in FULL OUTER JOIN (SELECT DISTINCT dbo.Call.Call_Id, dbo.Call.Open_Dt, '1' AS whatever, dbo.Company.Company_Name FROM dbo.Call INNER JOIN dbo.Call_Action ON dbo.Call.Call_Id = dbo.Call_Action.Call_Id INNER JOIN dbo.Response_Time ON dbo.Call.Priority_Id = dbo.Response_Time.Priority_Id AND dbo.Call_Action.Action_Dt >= DATEADD([hour], dbo.Response_Time.Response_Time, dbo.Call.Open_Dt) INNER JOIN dbo.Project ON dbo.Response_Time.SLA_Id = dbo.Project.SLA_Id INNER JOIN dbo.Company ON dbo.Project.Company_Id = dbo.Company.Company_Id WHERE (dbo.Call_Action.Action_Notes LIKE N'%changed the status of this Call from ''Pickup'' to%') AND (dbo.Call.Open_Dt > '06-20-2004')) AS Response_out ON Response_in.whatever = Response_out.whateverSorry about the mess....The resultset from this has 2 columns with no nulls in either and I know for sure that there is not the same number of rows in each of the 2 tables.Any help would be really really really appreciated - I've hit a wall.Thanks |
|
|
|
|
|
|
|
outer join?