Subquery to join another table rows more than once

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 2005 Forums

Transact-SQL (2005)

Subquery to join another table rows more than once

Author

Topic

JohnG1
Starting Member

2 Posts

Posted - 2009-06-24 : 01:00:49

I'm quite new to SQL, but seem to have a fair grasp of the basics. This one though has me stumped.

I have one table which lists Tasks and may have a couple of IDs for Team Members - ManagerID and AssignedID which relates to a Users table.

I'm trying to join these two tables (Tasks and Users) to include the Manager and Assigned person names.

eg.

TASKS
TaskID ManagerID AssignedID
1 1 2

USERS
UserID Name
1 Tom
2 Jerry

I'm struggling to return the Users.Name twice for both the ManagerID and AssignedID. The data that should be returned would be:

TaskID ManagerName AssignedName
1 Tom Jerry

Any help on how to achieve this would be appreciated.

Thanks

John

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-24 : 01:06:23

use table alias


select t.TaskID, m.Name as ManagerName, a.Name as AssignedName
from   TASKS t
       inner join USERS m on t.ManagerID  = m.UserID
       inner join USERS a on t.AssignedID = a.UserID

KH
[spoiler]Time is always against us[/spoiler]