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 |
|
johnc
Starting Member
5 Posts |
 Posted - 2003-03-19 : 21:22:21
|
| Have the following tables:Policy A, ClientOrganisation B, Company C, AdviserIDs I, Users UPerforming this join ok:select * from Policy A LEFT OUTER JOIN AdviserIDs I ON A.AdviserID = I.AdviserNo, ClientOrganisation B, Company C where A.ClientID = B.ClientID and B.CompanyID = C.CompanyID and .....I need to join it onto Users U ON I.UserNo = U.UserNoCan't see where I can do thisNeed to pull the users name out of users on a match with AdviserID which may or may not be in Policy...Any thoughts???Just managed to do it by doing this:select * from Policy A LEFT OUTER JOIN (select ID.AdviserNo, US.realname from AdviserIDs ID, Users US where ID.UserNo = US.UserNo) IU ON A.AdviserID = IU.AdviserNo, ClientOrganisation B, Company C where A.ClientID = B.ClientID and B.CompanyID = C.CompanyID and ...Seems a bit messy - any neater suggestions would be greatly received.~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"Thinking is the hardest work there is, which is the probable reason so few engage in it." - Henry FordEdited by - johnc on 03/19/2003 21:48:09 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-03-19 : 22:44:15
|
| Specing the JOINS requires knowing the keys in the tables to join..Select *From Policy ALEFT OUTER JOIN ClientOrganisation B ON b.??? = a.???LEFT OUTER JOIN Company C on C.???=A.???LEFT OUTER JOIN AdviserIDS I on I.???= and so on.------------------From the description you gave...SELECT * FROM POLICY ALEFT OUTER JOIN Advisors I ON A.AdviserID = I.AdviserNoLEFT OUTER JOIN ClientOrganisation B A.ClientID = B.ClientIDLEFT OUTER JOIN Company C ON B.CompanyID = C.CompanyIDLEFT OUTER JOIN Users U ON I.UserNo = U.UserNo WHERE whateverSamEdited by - SamC on 03/19/2003 22:52:43 |
 |
|
|
|
|
|
LEFT OUTER JOIN on a JOIN??