+ Start a Discussion
RedBirdOBXRedBirdOBX 

Using basic relationships (join) in SOQL

Hello all.  I've recently started a new job with a marketing firm here and have been thrown into the hot seat.  I have a strong SQL and SQL Server background but have never used SOQL or anything salesforce related.  Ever.  I'm only day three into this project but I'm finding this all very challenging and frustrating.  

I am simply trying to use a UI provided by SalesForce (Enterprise Editiion) called Workbench.  This allows me to query from tables...or I guess I should call them Objects.   I am simply trying to write a query where I need to inner join two tables with some conditions.  I have spent the past day or so investigating and reading several topics online regarding this but I am not making any progress.

I truly appreciate any help or feedback you can provide!

I have two questions:

1) When I write anykind of query using Workbench and select View As Bulk CSV, it only exports my actual query into a txt file.  Is this correct?  I was hoping to query out some basic data and use it in a local copy of SQL Server.

2) Here the important one.  Listed below is the query I've tried to write in several variations withouth any luck.  I do know for a act the relationship between the tables is Opportunity.ID (parent) >> Task.WhatID (child).

If I were to do this in any SQL environment, I would simply do this:

SELECT  Task.Subject, Opportunity.CreatedDate, Opportunity.StageName
FROM  Opportunity
INNER JOIN Task ON Opportunity.Id = Task.WhatID
WHERE  (Opportunity.StageName = 'Prospecting')
AND (Opportunity.Name NOT LIKE 'ACME')

Can someone please suggest the proper SOQL syntax?

Once again, THANKS for helping!



 


Best Answer chosen by RedBirdOBX
Saurabh DhobleSaurabh Dhoble
I'm from an SQL background as well, and the SOQL syntax drives me nuts. Wait until you get into group by joins and you'll pull your hair out.
Anyway, for your problem, I think this will work -

select id, Name, StageName, (select Id, Subject from Tasks) from Opportunity
where StageName = 'Prospecting' and (NOT Name like '%acme%')

The inner query gets all the "child" tasks linked to that opportunity. Check it out and let me know if it works (and mark this as an answer).

BTW, you might want to consider a product like DBAmp - it downloads all your Salesforce data into an SQL Server database, and allows you do CRUD operations from SQL, by calling their stored procs. Check it out, all our other systems talk to a SQL Server database for Salesforce data and it works like a charm.

All Answers

Saurabh DhobleSaurabh Dhoble
I'm from an SQL background as well, and the SOQL syntax drives me nuts. Wait until you get into group by joins and you'll pull your hair out.
Anyway, for your problem, I think this will work -

select id, Name, StageName, (select Id, Subject from Tasks) from Opportunity
where StageName = 'Prospecting' and (NOT Name like '%acme%')

The inner query gets all the "child" tasks linked to that opportunity. Check it out and let me know if it works (and mark this as an answer).

BTW, you might want to consider a product like DBAmp - it downloads all your Salesforce data into an SQL Server database, and allows you do CRUD operations from SQL, by calling their stored procs. Check it out, all our other systems talk to a SQL Server database for Salesforce data and it works like a charm.
This was selected as the best answer
RedBirdOBXRedBirdOBX
Saurabh - you are my hero for the day!  Thanks.  It worked.  Is this "DBAmp" provided by Salesforce or is it a 3rd part app?
Saurabh DhobleSaurabh Dhoble
No, it's not provided by Salesforce and is 3rd party. Basically, it's an .EXE pogram that runs on the Sql Server machine, and a set of corresponding stored procedures that invoke it using xp_cmdshell.
The biggest benefit is that you can bring down all your Salesforce data into a Sql Server database, and then run SSRS reports/SQL queries on the data to get around various limitations in Salesforce.
We also do a lot of updates - say you want to update 50 account records. You dump them into a table called "Account_{whatever text} .... and then call a stored proc like - 
exec sf_bulkops 'update', 'Salesforce', 'Account_whatever_text" ...

and it will update the set of rows in the table to Salesforce. I'm not good at explaining but I definitely recommend trying it out.

P.S. - I don't work for dbamp nor am I getting paid by them. I'm just a big fan.