CODEWITHSUNDEEP
sql-servert-sqljoin
Serdia
Serdia

Reputation: 4428

Why joining same table twice returns different column values?

I have a stored procedure that was not written by me.

I cannot understand how they are joining the same table tblUsers twice and it returns different column values:

select distinct 
    usr.Name_FirstLast AS AssignedTo,
    usr1.Name_FirstLast as AssignedBy 
from 
    dbo.tblNoteStore  nt_str
join 
    dbo.tblNoteEntities entit ON nt_str.ID = entit.NoteGUID
join
    dbo.tblNoteDiaries nt_dia ON nt_str.ID = nt_dia.NoteGUID
join
    dbo.tblNoteEntries entri on nt_str.ID = entri.NoteGUID
                             and nt_dia.EntryGUID = entri.ID 
                             and entit.NoteGUID = entri.NoteGUID
left join 
    dbo.tblNoteRecipients recip ON entri.ID = recip.EntryGUID
--this is where the same table used twice 
left join 
    dbo.tblUsers  usr ON recip.UserGUID = usr.UserGUID   -- returns AssignedTo column
left join
    dbo.tblUsers usr1 ON usr1.UserGuid = entri.UserGUID   -- returns AssignedBy column
where 
    usr.UserGUID = '55610B2F-1997-40C0-9F01-EED3ED2939F9'

The result is what I need, but why it happens that way?

enter image description here

Also, do I have to use all those tables in JOIN in order to receive the result?

Upvotes: 1

Views: 56

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46233

LEFT JOIN dbo.tblUsers  usr ON recip.UserGUID = usr.UserGUID --    > gives me AssignedTo column
LEFT JOIN dbo.tblUsers usr1 ON usr1.UserGuid = entri.UserGUID--gives me AssignedBy column

The UserGUID column of the 2 dbo.tblUsers table references are correlated using different columns (recip.UserGUID and entri.UserGUID) so different rows from dbo.tblUsers may be returned.

Upvotes: 3

Related Questions