2

I am querying a DE and trying to left join _Subscribers on SubscriberKey. I am getting results back from the left side, Master_DE, but nothing is coming back for the Status column. I am wondering if there is something wrong with my SQL? m.SubscriberKey is a text(100) DE field. I also know there should be data coming back because I have verified the matching rows exist.

SELECT m.personid, 
       m.[join_date], 
       s.status 
FROM   [master_de] m 
       LEFT JOIN _Subscribers s 
              ON m.SubscriberKey = s.SubscriberKey
WHERE  [join_date] > Dateadd(d, -7, Getdate()) 
       AND [join_date] < Dateadd(d, Datediff(dd, 0, Getdate()), 0) 
  • When you view your DE properties, what does the relationship to Subs say? For example, "EmailAddress relates to Subscribers on Subscriber Key." – Drew Simmons Feb 18 '15 at 21:06
2

If your ExactTarget account is an Enterprise account, try prefixing the system data view name with ENT.

SELECT ... 
FROM [master_de] m 
LEFT JOIN ENT.[_Subscribers] s 
ON m.SubscriberKey = s.SubscriberKey

See this Help article for more information.

  • This is the correct answer, however the table name should not be wrapped in brackets. It should be LEFT JOIN ENT._Subscribers. It will actually throw an error when you try to save the query. – Jon Sakas Feb 23 '15 at 19:08
0

Do you happen to have a Status column on your [master_de] data extension? What happens if you do a standard JOIN and not use the "Left Join"? Does this now populate with a status (Text-15 characters)?

SELECT m.personid, 
       m.[join_date], 
       s.status 
FROM   [master_de] m 
       JOIN _Subscribers s 
              ON m.SubscriberKey = s.SubscriberKey
WHERE  [join_date] > Dateadd(d, -7, Getdate()) 
       AND [join_date] < Dateadd(d, Datediff(dd, 0, Getdate()), 0) 

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.