SQL Server Performance Forum – Threads Archive

How to query to 2 tables for same row

Hi Lets assume we have a table with 2 columns that point to same column in 2nd table.
For example "Salary" table has 2 columns "usersID" and "UserBossId" that both points to userid column in "Users" Table. Now the question is How do i query the table to get the data in this format salary UserName UserBossName Thanks
Amrita

---

You need to join onto the 2nd table twice, and this means aliasing it so you can differentiate between them… select
s.Salary,
baseUser.Username,
bossUser.Username
from
Salary s
LEFT JOIN Users baseUser ON (s.usersID = baseUser.usersid)
LEFT JOIN Users bossUser ON (s.UserBossID = bossUser.usersid)

---

I guess column userId is mandatory (not nullable) in salary table, so inner join can be used instead.

---

apart from the CEO who presumably has no boss <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br />But yes, well spotted, baseUser at least could use inner join<br /><br /><br />

---

]]>