1

I have a table that holds Tasks details and a table that holds users details. Each Task has two relations with the users table as following:

Tasks table : TaskID, Name, Description, TaskCreatorId, TaskPICId Users table : UserId, Name, E-mail Where TaskCreatorId is linked to UserId, and TaskPICId is also Linked to UserId. Now how i make a query to list all tasks with Taskcreator name and TaskPIC name Tasks table

Users table

Emad Ali
  • 447
  • 1
  • 6
  • 11
  • 2
    hi please share sample data and expected result – Yashveer Singh Jan 18 '17 at 20:27
  • try my answer if any issues please let me know – Yashveer Singh Jan 18 '17 at 20:32
  • Hi @Emad Ali, welcome to Stackoverflow! Please do not take this personally, but you really should try and put a little more effort into searching the existing questions/answers before rephrasing an all too familiar problem again. Your problem was already discussed and solved more than 6 years ago, as you can see here: http://stackoverflow.com/questions/4267929/whats-the-best-way-to-join-on-the-same-table-twice – Carsten Massmann Jan 18 '17 at 21:02
  • @cars10 Thank u. Sorry I'm still new here and maybe i have used the wrong keyword for search – Emad Ali Jan 18 '17 at 21:21

2 Answers2

2

You need to join the user information to the task information at two different points.

SELECT 
    t.TaskID, 
    t.Name, 
    t.Description, 
    t.TaskCreatorId,  
    u1.Name as TaskCreatorName, 
    u1.[E-mail] as TaskCreatorEMail,
    t.TaskPICId,
    u2.Name as TaskPICName,
    u2.[E-mail] as TaskPICEMail
FROM 
    Tasks t 
    LEFT JOIN Users u1 ON u1.userId = t.TaskCreatorId
    LEFT JOIN Users u2 ON u2.userId = t.TaskPICId
Fritz
  • 624
  • 1
  • 7
  • 14
0

Try to tun this:

                        declare @tuser as table (id int , name varchar(10))
            declare @ttask as table (tid int , cid int )

            insert into @tuser(id,name) values(1,'a')
            insert into @tuser (id,name)values(2,'b')
            insert into @ttask  values(1,2)


            select u.name as nm1  , u1.name as cname, t.* from @ttask  t left join @tuser u on u.id = t.tid 
            left join @tuser u1 on   u1.id= t.cid
Yashveer Singh
  • 1,914
  • 2
  • 15
  • 23