join on the same field 2x - SQL Server Forums

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

Transact-SQL (2000)

join on the same field 2x

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-03-27 : 00:26:44

I have a table called "TBLSUSPENDREASON"

UserID --> int
SuspendReason --> varchar(100)
ModID --> int

I join the userID of "TBLSUSPENDREASON" onto "TBLUSERDETAILS" and get the associated record ("nameOnline"). My problem is that I want to join MODID as well onto the same table and get nameOnline as well.

this is what i have so far

SELECT suspend.userID, reason, modID, nameonline, nameOnline FROM tblSuspendReason suspend JOIN tbluserdetails on suspend.userID = tblUserDetails.userID WHERE suspend.userID = 5686

can somebody give me some direction on where to go ?? thanks

MIke

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-03-27 : 00:44:52

Hi Mike

You can join twice to the same table, the only trick is each time, you need to give that table an "alias" in your query. Otherwise, SQL Server doesn't know which one you are referring to.

Something like this



SELECT 
suspend.userID, reason, modID, 
u1.nameonline as SuspendedUser, 
u2.nameOnline  as Moderator

FROM

tblSuspendReason suspend 
INNER JOIN tbluserdetails u1 ON u1.UserID = suspend.UserID
INNER JOIN tbluserdetails u2 ON u2.UserID = suspend.ModID

WHERE

suspend.UserID = 5686