SQL Server Performance

How to query to 2 tables for same row

Discussion in 'T-SQL Performance Tuning for Developers' started by bhatia_amrita, Apr 11, 2006.

  1. bhatia_amrita New Member

    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
  2. Chappy New Member

    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)

  3. mmarovic Active Member

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

    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 />

Share This Page