SQL Server multiple left join workaround

Question

I need to fetch Created_By and Modified_By user names from master user table. I'm using Created_By_Id and Modified_By_Id in the table to look up master user table and fetching the employee name from it.

Currently my SQL query is:

select 
    act.*, created.name as created_by, modify.name as modified_by
from 
    [DUMP_ACTIVITY] act
left join 
    [MASTER_USER] created on act.Created_By_Id = created.id
left join 
    [MASTER_USER] modify on act.ModifiedById = modify.id

Now, I must left join the master user table two times repeatedly to do this. Is this correct?

Is there a workaround for this?

Yes, this **is** the way to go - and no, there's no "workaround" - workaround for what?? If you need to "lookup" two user accounts by their `Id`, you need to join to the table that contains the detail information for the user twice - absolutely perfectly valid and correct and not in need of any "workaround" .... — marc_s, Mar 21 '23 at 04:59

score 0 · Answer 1 · answered Mar 21 '23 at 08:58

There are several ways you can achieve this but it can be simple like below also:

select 
    act.*,
    (select [MASTER_USER].name FROM [MASTER_USER] WHERE [MASTER_USER].Created_By_Id = act.id) AS created_by, 
    (select [MASTER_USER].name FROM [MASTER_USER] WHERE [MASTER_USER].ModifiedById = act.id) AS modified_by
from 
    [DUMP_ACTIVITY] act

SQL Server multiple left join workaround

1 Answers1