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.


1 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)
  • Accepted answer as the suggested solution works so does the job. Would still be interested in detail of why my original code doesn't work as it should work if I replicated the publication lists as tables in SQL Server.
    – JimS
    Sep 15, 2022 at 8:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .