Suhas Suhas -4 years ago 101
SQL Question

How to select names from one table having id's in another table?

i have two tables



userid - username
1 ------------> a
2-------------> b
3-------------> c


companyid - companyname - CreatedBy - ModifiedBy
101 ------------> c1------------------->1---------------------->1
102-------------> c2------------------->2---------------------->3
103-------------> c3------------------->1---------------------->2

How can I get the names of user, i.e. username from table user_info in the place of CreatedBy and ModifiedBy in table company_info?


companyid - companyname - CreatedBy - ModifiedBy
101----> c1----> a-----> a
102----> c2----> b-----> c
103----> c3----> a-----> b

I have tried this:

select A.companyid, A.companyname, B.username as CreatedBy, ModifiedBy
from company_info A
inner join user_inform B
on (A.CreatedBy = B.userid)

Answer Source

LEFT JOIN the user_info table twice. First time to get the created by user, second time to get the modified by user.

select ci.companyid, ci.companyname, uc.username CreatedBy, um.username ModifiedBy
from company_info ci
left join user_info uc on ci.CreatedBy = uc.userid
left join user_info um on ci.ModifiedBy = um.userid

(LEFT JOIN just in case one of the users are missing.)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download