reduicng number of left outer joins

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

SQL Server Development (2000)

reduicng number of left outer joins

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-05-30 : 15:40:24

i have table of states
1-usa
2-israel
and so on...
and i have a talbe with diffrent columns which each of them holds a country id :

table1
autoincrement,2,54,33
autoincrement,1,14,3
....

is it possible not to do :
select.. from table1
left outer join states on table1.col1=states.id
left outer join states_1 on table1.col1=states_1.id
......

is there a way not to use so many left outer koin's on the same table?
thnaks in adbvance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-05-30 : 16:16:57

hmm, not sure if I completely understand your question, but I think the answer is no. If you have multiple references to the country table, then to retrieve data you would need a join for each foreign key field that you are trying to get info on:


country(countryID, countryName, countryData)
table1(table1ID, table1Data, country1ID, country2ID, country3ID)

   select t1.table1Data, c1.countryName, c2.countryName, c3.countryName
   from table1 t1
      left outer join country c1 on t1.country1ID = c1.countryID
      left outer join country c2 on t1.country2ID = c2.countryID
      left outer join country c3 on t1.country3ID = c3.countryID

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-05-30 : 16:27:29

ok thanks alot

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources