0

in my organisation we wanted to have a single customer view. Hence, I've created a Lookup field on Lead to contacts, and contacts have a related Leads list.

The lookup field is called "ContactId"

I have a query as below but it is not querying any results, even though I should be expecting 5.

SELECT
C.Id
L.Id AS LeadId

FROM
Contact_Salesforce C
INNER JOIN Lead_Salesforce L ON C.Id = L.ContactId

WHERE L.ContactId IS NOT NULL
AND L.ContactId <> ''

I've used Inner Join as we have legacy data that does not have the [Lead]ContactId field populated and are null. Id and LeadId are composite keys. I've had results come through when I made Id nullable however this would not give me the same as what I'm after. Am I missing something here? Is there a limitation between the two objects?

UPDATE So I found out the query is not honouring the join on the lookup field. Is there limitation between contact and lead that I'm unaware of? I am able use lookup relationship to query from other objects except for contact.

  • Have you checked how Marketing Cloud sees the data type of the Id on Contact and Lead record? – zuzannamj Sep 12 at 6:47
  • Hi @CodeRage, yes both are Text 18 digits - which is what I have on my DE. – s3329893 Sep 12 at 6:53
  • 1
    OK - when you query both those objects separately, do you get correct results for the Id and ContactId? – zuzannamj Sep 12 at 8:05
  • I found out that the data wasn't in the contact data extension, i had some synching issues! – s3329893 Sep 13 at 3:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.