CODEWITHSUNDEEP
sql-servert-sqljoin
Serdia
Serdia

Reputation: 4428

Why joining same table twice on two different columns 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: 0

Views: 444

Answers (2)

paparazzo
paparazzo

Reputation: 45096

start with clean up syntax and put the same table on left 

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

this is silly on so many levels

tblUsers is the PK there is absolutely not reason to left to join to it

usr.UserGUID = '55610B2F-1997-40C0-9F01-EED3ED2939F9' was killing the left join

from join tblNoteEntities entit ON entit.NoteGUID = nt_str.ID

we know entit.NoteGUID = nt_str.ID
but you are repeating 

 join tblNoteEntries entri 
      on entri.NoteGUID = nt_str.ID 
     and entri.NoteGUID = entit.NoteGUID

the best sense I can make of this

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

Upvotes: 1

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112512

Because the user table is joined to two differents tables. 

left join 
    dbo.tblUsers  usr ON recip.UserGUID = usr.UserGUID

joins to the alias recip which stand for dbo.tblNoteRecipients, where as 

left join
    dbo.tblUsers usr1 ON usr1.UserGuid = entri.UserGUID

joins to the alias entri which stand for dbo.tblNoteEntries.

Note that these two aliases are declared in previous joins:

                         |
join                     V
    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
                            ^
                            |

Upvotes: 1

Related Questions