php请加入帮助

Question

What i am trying to achieve is i have a company table that has 2 userid field(userid and updated userid) i am trying to use left join to get the 2 names from the UserTable here are the structure of my tables

[CompanyTable] 

CompanyID

UserID:int

UpdatedBy:int

[UserTable]


UserID:int

FullName:string

i got 1 working but can't get the second value here are the code i am trying to do please any help would be awesome!!

SELECT company.CompanyID, company.UserID, company.UpdatedBy, users.FullName
    FROM company 
    LEFT JOIN users ON users.UserID = company.UserID
    LEFT JOIN users ON users.UserID = UpdatedBy.UserID
    GROUP BY company.CompanyID

Answer 1

You can try as below. The Idea is to JOIN the same users table twice with different alias and use them while selecting the fields. You have 2 ids in the company table and you can use the column alias to make it more readable for example u2.FullName as FullName_UpdatedBy means the full name of the updated by user.

select
c.CompanyID,
c.UserID,
u1.FullName,
c.UpdatedBy,
u2.FullName as FullName_UpdatedBy
from company c
left join users u1 on u1.UserID = c.UserID
left join users u2 on u2.UserID = c.UpdatedBy
group by c.CompanyID