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 <> ''
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.