-
SQL Query Across Several Tables Help
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 expect to get Account names, and EVERY Contact associated with that account, who have at least one contact who is a Campaign member; The Campaign Name that the contact is a member of, AND if there is an Opportunity associated with the Account- The opportunity Name.
I cant figure out the logic. I am new to SQL. I am sure I am not capturing the data I want. 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/develop...erd_majors.htm
This is what I have come up with
SELECT DISTINCT c.Name ContactName
,a.Name AccountName
,o.Name OppName
FROM Contact c
INNER JOIN CampaignMember cm ON cm.ContactId = c.Id
INNER JOIN Account a ON a.Id = c.AccountId
INNER JOIN Contact c2 ON c2.AccountId = a.Id
RIGHT JOIN Opportunity o ON o.AccountId = a.Id
WHERE o.CreatedDate > cm.CreatedDate;
ORDER BY AccountName DESC;
Thank You.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|