Doing a join with SOQL

Question

Here is my SOQL problem.

Query 1:

Select 
  c.Date_Joined__c, 
  c.Email, 
  c.FirstName, 
  c.LastName, 
  c.regcode__c 
from Contact c WHERE c.regcode__c ='XXXXXXXXX'

Query 2:

Select 
  p.Account__c, 
  p.Date__c, 
  p.Points__c, 
  p.Description__c, 
  p.Code__c 
from Points__c p where p.Account__c ='YYYYYYYYYYYY' and (p.Points__c > 0) 
Order by p.Date__c DESC

The relationship between the two queries is that c.regcode__c will have the same value as p.Code__c.

I want to combine Query1 and Query2, so c.regcode__c = p.Code__c

I'm stuck, I can't seem to get the syntax right for SOQL. Is it even possible to do joins in the API?

Because of the lack of a `join` keyword in SOQL, I don't see any way you're going to be able to retrieve this data in a single query without a bit of Apex magic (you mentioned you're calling this via the API though). SOQL really only supports selecting data from multiple objects if there is an explicit lookup or master-detail relationship between them: http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_relationships.htm — JCD, Jul 13 '12 at 20:51

score 12 · Accepted Answer · edited Mar 13 '18 at 14:34

12

You can't really create a join per se but you can do some filtering using a syntax similar to this:

SELECT Id FROM Contact WHERE c.RegCode__c IN (SELECT p.Code__c FROM Account)

As long as the subquery in the WHERE clause is only returning a single value and codes is a filterable field this should work. Also, this doesn't work if you were trying to filter by the same object (i.e. Account to Account). You can add more criteria to the account side to match your example queries.

Again, this isn't a true join so you can't put the account fields from your subquery. But you can at least filter down your contacts.

edited Mar 13 '18 at 14:34

Christian

27,509
17
111
155

answered Jul 16 '12 at 14:55

John De Santiago

1,194
7
8

1

I think you should really check this [link](http://sivatejaforce.wordpress.com/2011/02/11/a-deeper-look-at-soql-and-relationship-queries/), you can indeed do joins – Juan Nunez Apr 05 '13 at 12:43
@Jaun, the example for an inner join uses a subquery as well. – wobbily_col Feb 01 '17 at 10:07

Doing a join with SOQL

1 Answers1