如何在子查詢 JOIN 條件中使用父表列？

我正在編寫一個 SQL 查詢，用於從多個表中提取記錄，以便向使用者發送更新或添加新值的電子郵件通知。

在下面的 SQL 查詢中，我正在獲取在各個表中更新的所有記錄，但是我想在每個 LEFT JOIN 狀態中為表AssociatedRecordID列添加一個條件檢查/JOIN，NotificationQueue這樣我只會從每個選擇表中提取匹配的記錄以增加查詢執行的性能。

例如：

LEFT JOIN Note nt WITH(NOLOCK) ON op.OpportunityID = nt.RecordID AND nt.NoteID = nq.AssociatedRecordID

這裡我想nq.AssociatedRecordID用於加入AND nt.NoteID = nq.AssociatedRecordID

詢問：

SELECT ops.* FROM NotificationQueue nq
   INNER JOIN (
       SELECT op.OpportunityID, op.Name AS [OpportunityName], ua.Email AS OwnerEmail, nt.NoteID, nt.Note,
           tks.TaskID, tks.ActivityType, ua.FirstName, ua.LastName, FORMAT(getdate(), 'M/dd/yyyy, hh:mm:ss tt') AS [datetime],
           lead.LeadID, lead.LeadName
       FROM Opportunity op WITH(NOLOCK) 
       INNER JOIN UserAccount ua WITH(NOLOCK) on op.owner = ua.UserID
       LEFT JOIN Note nt WITH(NOLOCK) ON op.OpportunityID = nt.RecordID AND nt.EntityID = 4 -- OPPORTUNITY
       LEFT JOIN (
           SELECT t.TaskID, t.TaskTypeID, t.OpportunityID, tt.Description AS [ActivityType] FROM Task t WITH(NOLOCK)
           INNER JOIN TaskType tt ON t.TaskTypeID = tt.TaskTypeID
       ) tks ON op.OpportunityID = tks.OpportunityID 
       LEFT JOIN (
           SELECT l.LeadID, l.Topic AS [LeadName], lo.AssociatedOpportunityId FROM LeadOpportunity lo WITH(NOLOCK)
           INNER JOIN Lead l ON lo.ParentLeadId = l.LeadID
           WHERE l.Active = 1
       ) lead ON op.OpportunityID = lead.AssociatedOpportunityId

       WHERE op.Active = 1 
   ) ops ON nq.RecordID = ops.OpportunityID        
WHERE nq.Processed = 'N'

輸出：

我從上面的查詢中得到了預期的記錄，但我想在所有語句中使用 Table 的AssociatedRecordID列以獲得更好的性能，任何建議我應該如何實現同樣的效果將不勝感激。NotificationQueue``LEFT JOIN

您可能希望將EXISTS 運算符與子查詢一起使用，但在不同的情況下服務，在您的情況下，將NotificationQueue表向下移動到子查詢可以讓您執行您正在尋找的連接，以下是相同的範例：

SELECT  op.OpportunityID, 
       op.Name AS [OpportunityName], 
       ua.Email AS OwnerEmail, 
       nt.NoteID, nt.Note,
       tks.TaskID, 
       tks.ActivityType, 
       ua.FirstName, 
       ua.LastName, 
       FORMAT(getdate(), 'M/dd/yyyy, hh:mm:ss tt') AS [datetime],
       lead.LeadID, lead.LeadName
FROM NotificationQueue nq
   INNER JOIN  Opportunity op WITH (NOLOCK) ON nq.RecordID = op.OpportunityID  
   INNER JOIN  UserAccount ua WITH (NOLOCK) on op.owner = ua.UserID
   LEFT JOIN   Note nt WITH (NOLOCK) ON op.OpportunityID = nt.RecordID AND nt.EntityID = 4 -- OPPORTUNITY
   LEFT JOIN   (
               SELECT  t.TaskID, t.TaskTypeID, 
                       t.OpportunityID, 
                       tt.Description AS [ActivityType] 
               FROM    Task t WITH(NOLOCK)
                       INNER JOIN TaskType tt ON t.TaskTypeID = tt.TaskTypeID
               ) tks ON op.OpportunityID = tks.OpportunityID

   LEFT JOIN   (
               SELECT  l.LeadID, 
                       l.Topic AS [LeadName], 
                       lo.AssociatedOpportunityId 
               FROM    LeadOpportunity lo WITH(NOLOCK)
                       INNER JOIN Lead l ON lo.ParentLeadId = l.LeadID
               WHERE l.Active = 1
               ) lead ON op.OpportunityID = lead.AssociatedOpportunityId

WHERE op.Active = 1 and nq.Processed = 'N'

---

根據評論，以下是 last 的範例LEFT JOIN：

   OUTER APPLY (
               SELECT  l.LeadID, 
                       l.Topic AS [LeadName], 
                       lo.AssociatedOpportunityId 
               FROM    LeadOpportunity lo WITH(NOLOCK)
                       INNER JOIN Lead l ON lo.ParentLeadId = l.LeadID
               WHERE l.Active = 1 AND nq.RecordID = lo.AssociatedOpportunityId
               ) lead --ON op.OpportunityID = lead.AssociatedOpportunityId

引用自：https://dba.stackexchange.com/questions/249337