Sql-server – How to use parent table column inside subquery JOIN condition

join;performancequery-performancesql server

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.

Best Answer

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
Related Question