How to JOIN 2 Tables in SalesForce (SOQL)

Question

I'm new to SalesForce and SOQL and was surprised that simple JOIN can become a real problem for me.

There are 2 Tables (Account and Intake__c) that I want to INNER JOIN. The only data I need from Account Table is Client Name (Name).

I was able to run 2 queries separately with no errors.

Account:

SELECT 

     a.Id, 
     a.Name

FROM Account AS a

Intake__c:

SELECT
      
      i.Client_Id__c,
      i.Intake_Id__c,
      i.Intake_Name__c,
      i.Intake_Status__c
      
FROM Intake__c AS i

However, when I try to join them, I get the error:

MALFORMED_QUERY: ERROR at Row:1:Column:151 unexpected token: 'JOIN'

SELECT
      
      i.Client_Id__c,
      a.Name,
      i.Intake_Id__c,
      i.Intake_Name__c,
      i.Intake_Status__c
      
FROM Intake__c AS i

JOIN Account AS a

    ON (i.Client_Id__c = a.Id)

Answer 1

SOQL syntax for joins is special, looks bit object-oriented. https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_and_custom_objects.htm

You probably need

SELECT Client_Id__r.Name, Intake_Id__c, Intake_Name__c, Intake_Status__c
FROM Intake__c

The "__r" bit is called relationship name and acts bit like a table alias in JOIN. You can travel via "dot" up to 5 times (see, "object-oriented").

And if you'd need a top-down approach (left outer join starting from account) it'd probably be something like

SELECT Id, Name,
   (SELECT Intake_Status__c FROM Intakes__r)
FROM Account