I found this link Can I query to all contacts in contact builder
and it was very helpful but it only returns the subscriber keys. I want to query emails for duplicates since contactkeys - there are no duplicates.
Is there some way to extract email from the above instead of ONLY subscriberkey?
To add, I have a list in mobile connect that has the rule email address is NOT null
You can simply extend the query in step 4 of the linked approach with a join on the _Subscribers data view based on the subscriberkey that exists in both sources to pull the email address into the target data extension.
It would look something like this:
SELECT a.subscriberkey, b.emailaddress from [name of MobileConnect/MobilePush filtered list name] a JOIN _Subscribers b ON a.subscriberkey = b.subscriberkey
This works for contacts who have been emailed and got added to All Subscribers. If you need to match with a data extension that holds your contacts regardless if they have been emailed you need to join this data extension instead. Follows the same principle but the field names for subscriberkey and emailaddress might be different and from your question it looks like you should add a WHERE statement that checks if the email field is not empty.
