Two Foreign Keys of a Table Referring a Same Primary Key

tryMe2c
tryMe2c used Ask the Experts™
on
i have a TB_Users table having userid(primary key) and UName (i.e.,username). I have another table TB_Devices having ProjectManager, & ReceivedBy fields referring to UserID of Users table. Now i have created a view via SQL Server Management Studio, & here is the code automatically generated:

SELECT     dbo.TB_Devices.ProjectName, dbo.TB_Devices.ProjectManager, dbo.TB_Devices.ReceivedBy, dbo.TB_Users.UName
FROM         dbo.TB_Users INNER JOIN
                      dbo.TB_Devices ON dbo.TB_Users.UserID = dbo.TB_Devices.ProjectManager AND dbo.TB_Users.UserID = dbo.TB_Devices.ReceivedBy

the problem is this code will give me only those rows where both ProjectManager and ReceivedBy are the same.

I found some results on google telling me to use aliases to distinguish between ProjectManager & the ReceivedBy fields in the Users table, but I couldn't do it
Can anyone tell me how to get through this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I would have my query as follows
SELECT     dbo.TB_Devices.ProjectName, pm.UName AS ProjectManager, RecBy.UName AS ReceivedBy
FROM         
                      dbo.TB_Devices 
LEFT JOIN dbo.TB_Users pm ON pm.TB_Users.UserID = TB_Devices.ProjectManager 
LEFT JOIN dbo.TB_Users  RecBy ON RecBy.TB_Users.UserID = TB_Devices.ProjectManager

Open in new window

Commented:
Try this.
SELECT
	a.ProjectName
	,a.ProjectManager
	,a.RecievedBy
	,b.UName ProjectManagerName
	,c.UName RecievedByName
FROM
	dbo.TB_Devices a
	INNER JOIN dbo.TB_Users b
		a.ProjectManager = b.UserID
	INNER JOIN dbo.TB_Users c
		ON a.RecievedBy = c.UserID

Open in new window

Author

Commented:
sorry but it didn't work with me in either way

@tim_cs
I got this error message after running your query like in the image:

Error Message Image

http://imageshack.us/photo/my-images/705/errormsgm.jpg/

Commented:
I missed an ON in mine.
SELECT 
        a.ProjectName 
        ,a.ProjectManager 
        ,a.RecievedBy 
        ,b.UName ProjectManagerName 
        ,c.UName RecievedByName 
FROM 
        dbo.TB_Devices a 
        INNER JOIN dbo.TB_Users b 
                ON a.ProjectManager = b.UserID 
        INNER JOIN dbo.TB_Users c 
                ON a.RecievedBy = c.UserID

Open in new window

Author

Commented:
Beautiful :D
Just aside note, you misspelled the ReceivedBy as RecievedBy, but I corrected it, & then it worked like magic

Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial