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

user_inform
and
company_info
.

Table
user_info
:

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


Table
company_info
:

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?

I.e.

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