SFMC Query: Never received/Never received an email that contains - single query

Question

i am wanting to join two Quries together so they work as one rather than independently (both work as intended on their own)

Query 1 The first one will select all subscribers who are customers and have never received an email containing newsletter and only in a certain list.

SELECT l.[EmailAddress], e.[First_name]
FROM _Job j with (nolock)
INNER JOIN _Sent s with (nolock)
ON j.JobID = s.JobID
INNER JOIN _ListSubscribers l with (nolock)
ON l.subscriberID = s.subscriberID
INNER JOIN _EnterpriseAttribute e with (nolock)
ON l.subscriberID = e._subscriberID
WHERE s.EventDate < GetDate()
AND e.Segment = 'Customer'
AND j.EmailName NOT LIKE 'newsletter_%'
AND l.ListID = 919445

Query 2 The second one will select all subscribers who have never received an email ever from the system, are customers and only in a certain list.

SELECT l.[EmailAddress], e.[First_name]
FROM _ListSubscribers l with (nolock)
INNER JOIN _EnterpriseAttribute e with (nolock)
ON l.subscriberID = e._subscriberID
WHERE NOT EXISTS (SELECT * FROM _Sent WHERE _Sent.[subscriberID] = l.[subscriberID])
AND e.Segment = 'Customer'
AND l.ListID = 919445

what i am trying to acheive is one query that will select all subscribers who have:

• Never received an email before or never received an email that contains the words newsletter and are customers all from the same list.

Thank you in advance

Answer 1

Query 1

Using a UNION should get you the results you want an not duplicate.

SELECT l.[EmailAddress], e.[First_name]
FROM _Job j with (nolock)
INNER JOIN _Sent s with (nolock)
ON j.JobID = s.JobID
INNER JOIN _ListSubscribers l with (nolock)
ON l.subscriberID = s.subscriberID
INNER JOIN _EnterpriseAttribute e with (nolock)
ON l.subscriberID = e._subscriberID
WHERE s.EventDate < GetDate()
AND e.Segment = 'Customer'
AND j.EmailName NOT LIKE 'newsletter_%'
AND l.ListID = 919445

UNION

SELECT l.[EmailAddress], e.[First_name]
FROM _ListSubscribers l with (nolock)
INNER JOIN _EnterpriseAttribute e with (nolock)
ON l.subscriberID = e._subscriberID
WHERE NOT EXISTS (SELECT * FROM _Sent WHERE _Sent.[subscriberID] = l.[subscriberID])
AND e.Segment = 'Customer'
AND l.ListID = 919445

Warning:

Depending on the size of your list this might time out. Unions take alot longer

Query 2

SELECT l.[EmailAddress], e.[First_name]
FROM _Job j with (nolock)
INNER JOIN _Sent s with (nolock)
ON j.JobID = s.JobID
INNER JOIN _ListSubscribers l with (nolock)
ON l.subscriberID = s.subscriberID
INNER JOIN _EnterpriseAttribute e with (nolock)
ON l.subscriberID = e._subscriberID
LEFT JOIN _Sent T ON l.subscriberID = T.subscriberID 
WHERE s.EventDate < GetDate()
AND e.Segment = 'Customer'
AND j.EmailName NOT LIKE 'newsletter_%'
AND l.ListID = 919445
AND T.subscriberID IS NULL

Query 3

Personally, I would keep the queries separate as there are alot of joins here and it might not perform well as your database scales.

Keeping the Assuming your 1st query is is DataExtensionA and your 2nd in DataExtensionB:

SELECT
EmailAddress
,First_Name

FROM DataExtensionA

UNION

SELECT
EmailAddress
,First_Name

FROM DataExtensionB