SQL Activity Query - return subscribers from publication list if they don't exist in another publication list

Question

I'm currently wanting to copy a subset of subscribers from one publication list into another. The flow of the automation is planned to be something like this:

• SQL Activity to populate a data extension with subscribers that need to be copied
• Data extract activity to export csv of these contacts to safe house
• File transfer activity to move file from safehouse to location on FTP
• File import activity to load contents of CSV into new list.

The plan is currently failing at the first hurdle and the query I've written in the SQL activity isn't populating the data extension with any subscribers. This is the query I'm using.

SELECT gm.EmailAddress,gm.SubscriberKey,gm.Status
FROM _ListSubscribers gm
LEFT JOIN _ListSubscribers cr
ON gm.SubscriberKey = cr.SubscriberKey
WHERE cr.SubscriberKey IS NULL
AND cr.ListID = 37464
AND gm.ListID = 35439

Is it possible to join two publication lists together in this way in a SQL query? If so why might the query not be returning any results?

The list with ID 37464 is currently empty and the list with ID 35439 has 9709 subscribers in it.I'm expecting that the data extension will be populated with all the 9709 subscribers on the left side of the join as none of them are present in the publication list on the right side.

Thanks

Answer 1

You’re using two and operators in your filter which is probably why it’s failing. I’d approach it slightly differently and instead of a join, use a condition to return subscribers from one list if they don’t exist in the second one:

SELECT EmailAddress,SubscriberKey,Status
FROM _ListSubscribers
WHERE ListID = 35439 
AND SubscriberKey NOT IN 
(SELECT SubscriberKey 
FROM _ListSubscribers
WHERE ListID = 37464)