SQL Query across several tables in SalesForce

Question

The question that I am trying get get the data for is-

“I want to know when an Opportunity that has been influenced by a Marketing campaign is associated with an Account. There could be several contacts for the Account
So if ANY Contact is associated with a Campaign (Member of a campaign)- Tell me what Account the (or Those) Contact(s) is connected to, and if there is an Opportunity associated with the account, and if there is an Opportunity associated with the Account the Opportunity was created after the Contact was associated with the campaign”

I cant figure out the logic. I am new to SQL. ANY help would be greatly appreciated.

I am using SQL Server 2012

I have 5 relevant tables from a SalesForce database –

Account Table (PK ID, OpportunityID), Opportunity Table (PK ID, AccountID ), Contact Table (PK ID, AccountID), Campaign Table (PK ID, ContactID, ParentID), CampaignMember Table (PK ID, ContactID, CampaignID)

This link gives the SalesForce Data Model http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_erd_majors.htm

This is what I have come up with, but it returns no rows.

SELECT O.CreatedDate OppCreatedDate
,o.Name as OpportunityName
,a.Name AccountName
,c.Name as ContactName
,ca.Name as CampaignName
,o.Amount_Software
,o.CreatedDate
FROM Opportunity o
INNER JOIN Account a on a.Id = o.AccountID 
LEFT JOIN  Contact c ON c.AccountId = a.Id
INNER JOIN CampaignMember cm ON cm.CampaignID  = c.ID
INNER JOIN Campaign ca ON ca.ID = cm.CampaignID
LEFT JOIN Campaign ca2 ON ca2.ID = ca.ParentID
WHERE o.CreatedDate > cm.CreatedDate
ORDER BY AccountName DESC;

Thank You.

How are you executing this query? Salesforce has its own database querying language called SOQL. Unless you have imported data from Salesforce into SQL Server the T-SQL that you've written won't work. — Justin Helgerson
There is a Replica database I can connect SQL Server 2012 to. I can use SQL Server to query the data....I just cant figure out how to write it. — BeRye

Chains Chains · Accepted Answer · 2013-02-19T22:03:31

You're running an INNER JOIN on a LEFT JOIN, which is ambiguous.

Opportunity <--> Account
Account --> Contact
Campaign Member <--> Contact (this should be a left-join because of the line above)
Campaign Member <--> Campaign (this should be a left join because of the line two-above)
Campaign --> Campaign

The visual for this is here (you can't inner-join a new table to a left-joined table):

GOOD:
INNER JOIN <--> INNER JOIN --> LEFT JOIN --> LEFT JOIN

BAD:
INNER JOIN <--> INNER JOIN --> LEFT JOIN --> INNER JOIN

SQL Query across several tables in SalesForce

1 Answers