Doing a join with SOQL

Question

So, as you 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?

Answer 1

You actually can't really create a join but some filtering using a syntax like this is somewhat possible:

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

The main idea is that the subquery in the WHERE clause returns only a single value and the codes are in a filterable field. But, if you're trying a filter by the same object, it won't work. One more criterion can be added to the accounting side in order to match your example queries.