function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Wm Peck 1958Wm Peck 1958 

Can you join tables when writing SQL (SOQL) statements in Salesforce?

I am new to Salesforce and come from a reporting / SQL background and perspective. I started to look at how to write SQL (SOQL) in Salesforce.

This link answered my question exactly - it's painful. So that gave me the quick answer, and a headache as well.

On this link it describes a limitation of Salesforce in regards to SQL:
SOQL doesn’t support all advanced features of the SQL SELECT command. For example, you can’t use SOQL to perform arbitrary join operations, use wildcards in field lists, or use calculation expressions.

So my specific question is now, what does it mean "you can't use SOQL to perform arbitrary join operations"? Is this the same as simply saying "You can't do join operations"? What exactly is the adjective "arbitray" trying to convey.

And per the first link of the guy who was "OMG how do I write a normal SQL statement in Salesforce?", any suggestions on how to get started? I do have the Trailheads, and I see SOQL modules in "Apex Basics & Database", but do you have any tips on getting started.

My perspective is Oracle SQL and BusinessObjects for your traditional reporting.

Also, I think this is my first post, I don't see any Category for "Reporting", "Einstein", etc. So I put it in 
 
Best Answer chosen by Wm Peck 1958
Colin KenworthyColin Kenworthy
I think it just means you can't just pick any two tables and join them based on them both having a common column.  In the Where clause you also can't test for one table column being equal to another table column.
In Salesforce you join tables together by adding Lookup or Master-Detail fields to a table (SObject). This becomes a FK to the PK of the other table in the Lookup.  The PK in every Salesforce table is the "Id" column. Given any database record Salesforce can return you all the records that have a FK to it.
Suppose I create two SObjects Bus (Bus__c) and Depot (Depot__c).  I can only query Bus and Deport records separately as they are not joined tables.
e.g. Select Id,Name,Fuel__c,Seats__c from Bus__c

Now I add a Lookup field to Bus__c calling it "Home Base" (Home_Base__c).  This does two things, it creates a Relationship from Bus to Depot called Home_Base__r (note it's 'R' on the end not 'C'), it also creates a relationship the other way which you get to name yourself as part of making the Lookup field. Say I call this Busses (Busses__r).

So now I can query on the Bus table and add in Depot fields:
Select Id, Name, Seats__c, Home_Base__r.Name, Home_Base__r.Postcode__c from Bus__c
I can query the Depots and get the Busses in each
Select Id, Name, Postcode__c, (Select Name,Seats__c from Busses__r)  from Depot__c

For reporting I can make custom report types "Depots with Busses" and "Busses", they should let you get all your reports on the two tables.

All Answers

Wm Peck 1958Wm Peck 1958
I can't see how to edit my question? even before anyone responded ...
Colin KenworthyColin Kenworthy
I think it just means you can't just pick any two tables and join them based on them both having a common column.  In the Where clause you also can't test for one table column being equal to another table column.
In Salesforce you join tables together by adding Lookup or Master-Detail fields to a table (SObject). This becomes a FK to the PK of the other table in the Lookup.  The PK in every Salesforce table is the "Id" column. Given any database record Salesforce can return you all the records that have a FK to it.
Suppose I create two SObjects Bus (Bus__c) and Depot (Depot__c).  I can only query Bus and Deport records separately as they are not joined tables.
e.g. Select Id,Name,Fuel__c,Seats__c from Bus__c

Now I add a Lookup field to Bus__c calling it "Home Base" (Home_Base__c).  This does two things, it creates a Relationship from Bus to Depot called Home_Base__r (note it's 'R' on the end not 'C'), it also creates a relationship the other way which you get to name yourself as part of making the Lookup field. Say I call this Busses (Busses__r).

So now I can query on the Bus table and add in Depot fields:
Select Id, Name, Seats__c, Home_Base__r.Name, Home_Base__r.Postcode__c from Bus__c
I can query the Depots and get the Busses in each
Select Id, Name, Postcode__c, (Select Name,Seats__c from Busses__r)  from Depot__c

For reporting I can make custom report types "Depots with Busses" and "Busses", they should let you get all your reports on the two tables.
This was selected as the best answer
Wm Peck 1958Wm Peck 1958
Colin,

ok that's helpful. Looks like it's a new "paradigm", and way different ... :-)