+ Start a Discussion
mromani1mromani1 

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!

SuperfellSuperfell

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

 

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

mtbclimbermtbclimber

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
mromani1mromani1
Thanks a lot guys for the help! Appreciate it!
mromani1mromani1
Thanks a lot guys for the help! Appreciate it!!