Skip to main content
improved explanation
Source Link
0xsegfault
  • 6.6k
  • 2
  • 32
  • 69

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

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 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
improved explanation
Source Link
0xsegfault
  • 6.6k
  • 2
  • 32
  • 69

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

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 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
Source Link
0xsegfault
  • 6.6k
  • 2
  • 32
  • 69

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