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?

Answer 1

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.