CODEWITHSUNDEEP
sqlsql-server
Suhas
Suhas

Reputation: 83

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)

Upvotes: 0

Views: 2253

Answers (3)

Will Jobs
Will Jobs

Reputation: 360

Mansoor (above) is close:

SELECT
    c.companyid, c.companyname, u_c.username AS CreatedBy, u_m.username AS ModifiedBy
FROM 
    (company_info c INNER JOIN user_info u_c ON company_info.CreatedBy = u_c.userid)
    LEFT JOIN user_info u_m ON company_info.ModifiedBy = u_m.userid

Upvotes: 0

jarlh
jarlh

Reputation: 44766

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.)

Upvotes: 2

Mansoor
Mansoor

Reputation: 4192

Try below query:

SELECT *
FROM table1
JOIN User_table T1 ON table1.CreatedBy = T1.userid
LEFT JOIN User_table T2 ON table1.ModifiedBy= T2.userid

Upvotes: 0

Related Questions