Joining Tables Problem with two references to same column (Inner Join?) -- sql server programming

rcolby
8/20/2004 11:33:29 PM

Hello All,

Please bear with me if this doesn't make sense, this is all new to me and
never really worked with SQL before other than an oracle module at uni (well
forgotten by now).

I have these three table for example :

Table 1 Table 2
Table 3
-------- --------
--------
UserID RecordID
UserAppID
DisplayName AllocatedUserID (Ref to Table1)
UserID (Ref to Table 1)
UserAppID (Ref to Table 3)
Active
Description

What I would like to do is select all the fields in Table 2 with the fields
AllocatedUserID and UserAppID displaying the corresponding DisplayName from
Table 1. e.g. see select of table two below. The problem is UserAppID is the
displayName is referenced within Table3.

Select
Record ID AllocatedUserID UserAppID
Description
----------------------------------------------------------------------------
-----
1 User Three User One
Record 1
User One User Two
Record 2
3 User Two User Two
Record 3

The table with records above for easier understanding.

See example data tables below that genered the above.
Table 1 (Records)
UserID DisplayName
-----------------------------
1 User One
2 User Two
3 User Three

Table 2 (Records)
Record ID AllocatedUserID UserAppID
Description
----------------------------------------------------------------------------
-----
1 3 2
Record 1
2 1 3
Record 2
3 2 3
Record 3

Table 3 (Record
UserAppID UserID
------------------------------
1 3
2 1
3 1

Many Thanks
Richard

John Bell
8/21/2004 10:03:29 PM

Hi

Books online is the best place to look when trying to find information
especially when you have previous experience of a relational database.

It is always better to post proper DDL ( Create table statements etc...
http://www.aspfaq.com/etiquette.asp?id=5006 ) and example data ( as INSERT
statements http://vyaskn.tripod.com/code.htm#inserts ) with your current
efforts and expected results.

SELECT T2.RecordID, U1.DisplayName AS AllocatedUserName, U2.DisplayName
FROM TABLE2 T2
JOIN Table1 U1 ON T2.AllocatedUserId = U1.UserId
JOIN Table3 T3 ON T2.UserAppID = T3.UserAppID
JOIN Table1 U2 ON T3.UserId = U2.UserID

John

[quoted text, click to view]

sql server programming : Joining Tables Problem with two references to same column (Inner Join?)