How to use parent table column inside subquery JOIN condition?

Question

I am writing a SQL Query for pulling records from multiple tables for sending the update or added new values email notifications to the user.

In below SQL Query I am getting all records which are updated in respective tables, But I want to add one conditional check/JOIN in each LEFT JOIN satatements for AssociatedRecordID Column of NotificationQueue Table so that I will only pull only matched records from each select tables to increase the performance of query execution.

For example:

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

Here I want to use nq.AssociatedRecordID for Join AND nt.NoteID = nq.AssociatedRecordID

Query:

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'

Output: enter image description here

I am getting expected records from above query but I want to use AssociatedRecordID column of NotificationQueue Table in all LEFT JOIN statements for better performance, Any suggestions How should I achieve same would be appreciated.

Answer 1

You may want to use EXISTS operator with Sub-query, but that serve in different scenarios, in your case moving NotificationQueue table down to the sub-query can make you to do join that your looking for, following is the example of same:

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'

---

As per comment, following is the example of 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

• Rahul Hendawe: But still I can not use nq.AssociatedRecordID inside Left JOIN subqueries which will cause to read complete rows of those tables. for eaxample see last LEFT JOIN to Lead Table.
• Shekar Kola: Hi @RahulHendawe, you cannot directly do that in LEFT JOIN, if you got direct relationship between nq and lo tables, you can switch LEFT JOIN to OUTER APPLY in which case ON clause columns goes into WHERE clause of sub-query (pls look at my edited post). However, i don't think that would be optimal unlike CROSS APPLY, you may look at sys.dm_exec_query_stats after executing two queries side by side.
• Rahul Hendawe: OUTER APPLY worked for me.. Could you please add explanation in answer about how OUTER APPLY works?