Joining to a second table multiple times

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)

Joining to a second table multiple times

Author

Topic

jpc82
Starting Member

8 Posts

Posted - 2006-01-11 : 08:38:47

I am working on an app using MS SQL and I am not sure how best to create a view I need. I have one table which has 3 fields ( user1, user2, user3), and another table which has two fields (userid, username).

In my first table I want to store the user id's in the 3 fields, and then create a view which will have the actual username.

eg
Table 2
Userid UserName
1------Mike
2------John
3------Tom

Table 1
User1 User2 User3
1-----2-----3

I want the view to have
Userid1 UserName1 Userid2 UserName2 Userid3 UserName3
1-------Mike------2-------John------3-------Tom

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-11 : 08:41:59

you can achieve using table alias

select *
from table1 t1 inner join table2 t2a
     on t1.user1 = t2a.userid
inner join table2 t2b
     on t1.user2 = t2b.userid
inner join table2 t2c
     on t1.user3 = t2c.userid

-----------------
'KH'
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.

jpc82
Starting Member

8 Posts

Posted - 2006-01-11 : 08:57:14

Thanks, that makes sence, I did't think of that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources