Inner join on multiple columns in SQL Server

I have two tables

tbltask
tbluser

In tbluser I have two columns, userid and username

Sample data:

1     ibrahim
2     nizam
3     shahrukh
4      saddam

and in my second table, I have multiple tasks

taskid   userid   assignby   assignto
-------------------------------------
1         4           4          2

Here saddam assigned a task to nizam

2         3           3          1

Here shahrukh assigned a task to ibrahim

I have a select command like

select userid, assignby, assignto from tbltask where taskid='1'

and i am getting this

3        3          1

now i want to join the table, because instead of i, i want username to be displayed

so answer which i want to display will be

shahrukh shahrukh ibrahim

how i can join the table?

Answer

Here is the query

select t2.username, t3.username, t4.username
from tbltask t1
inner join tbluser as t2 on t1.userid = t2.userid
inner join tbluser as t3 on t1.assignby = t3.userid
inner join tbluser as t4 on t1.assignto = t4.userid

Advertisement

Answer