1

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

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
  • thank you very much for your reply, ill take a look at union and see how quick it is, it will only be ran once a month – odd Jun 1 '17 at 13:00
  • It's not about how frequently it is run but how long it takes. Any query longer than 10 minutes times out – Samuel Dare Jun 1 '17 at 13:07
  • * 30 minutes times out – Samuel Dare Jun 1 '17 at 13:24
  • thank you very much, i went with two and then used union to create the final list – odd Jun 1 '17 at 14:21
  • Query 3? Glad to have helped! – Samuel Dare Jun 1 '17 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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