1

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.

Improve this question

1 Answer 1

Reset to default
2

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
Improve this answer
3
  • 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.
    – Rahul Hendawe
    Sep 22, 2019 at 13:23
  • 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.
    – Shekar Kola
    Sep 22, 2019 at 17:06
  • OUTER APPLY worked for me.. Could you please add explanation in answer about how OUTER APPLY works?
    – Rahul Hendawe
    Sep 26, 2019 at 5:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.