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'
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.