function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion

Inner Join

I would like to know if its possible to do an inner join in Salesforce?


For example, i have an Invoices and Orders table, which are linked together.

Invoices has a field 'OrderID' which links it to the Orders table.

Now, they are not in a parent child relationship, just linked through a lookup field.


I would like to do something equivalent to:


Select a.invoiceID from Invoices__c as a

join orders__c as b on

where b.Currency='US'


so, select all invoices which have a currency of US (get that through the order table).


So far, i tried a few ways, but with no success...thats why i would like to know if its even possible to do this, and if so, how?


Is the only possible way = parent-child relationship?


Thanks for the help!


You can use the SOQL-R features to do the same thing


select Id from invoices__c where orders__r.currency='US'


The element you are missing is the relationship name. Lookups and Master/Detail (parent/child) relationships can both be traversed in the same way. Assuming you named your Order object "Order" ("Order__c" in the API) and selected all the defaults in the custom field wizard when you created the lookup from invoice to order, your SOQL would look something like this:


SELECT Id FROM Invoice__c WHERE Order__r.CurrencyISOCode = 'USD'


Note the "__r" which denotes the relationship vs. "__c" which denotes the field containing the ID of the related record.  You can traverse upwards like this 5 levels in the select or where clause. Check out the API documentation for more details. The section titled "Relationship Queries" is a good place to start.

Message Edited by mtbclimber on 12-30-2009 12:57 PM
Thanks a lot guys for the help! Appreciate it!
Thanks a lot guys for the help! Appreciate it!!