为什么在两个不同的列上两次连接同一张表会返回不同的列值? [英] Why joining same table twice on two different columns returns different column values?

问题描述

我有一个不是由我编写的存储过程.

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

我无法理解他们如何将同一张表tblUsers连接两次，并且返回不同的列值:

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?

• dbo.tblUsers具有主键UserGUID
• dbo.tblNoteEntries具有外键UserGUID
• dbo.tblNoteRecipients具有外键UserGUID

• dbo.tblUsers has primary key UserGUID
• dbo.tblNoteEntries has a foreign key UserGUID
• dbo.tblNoteRecipients has a foreign key UserGUID

此外，我是否必须使用JOIN中的所有那些表才能接收结果?

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

推荐答案

以清理语法开始，并将同一表放在左侧

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是PK，绝对没有理由放弃加入

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

usr.UserGUID ='55610B2F-1997-40C0-9F01-EED3ED2939F9'正在杀死左连接

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

来自 加入tblNoteEntities实体 ON entit.NoteGUID = nt_str.ID

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

我们知道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  

我对此有最深刻的认识

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

这篇关于为什么在两个不同的列上两次连接同一张表会返回不同的列值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！