Apex Code Development You need to sign in to do that
Don't have an account?
RedBirdOBX 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!
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!
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
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.
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.