Apex Code Development You need to sign in to do that
Don't have an account?
mromani1 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 a.orderid=b.id
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.