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 2

My original question was this:



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?




and it was answered very well, i user the __r relationship to traverse and do the select statement properly.

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


Now i have another related question:


Is it possible to join the other way around? EX: Go from Orders__c table to Invoices__c?

The lookup relationship is in Invoices, which is why it worked so easily going from Invoices to Orders, but now i would like to go the other way around...


I would think it would look something like:


Select count() from orders__c where related invoices__c.DateOrdered == Today() <---kinda pseudo coded....



Yes, that can be done through the use of a subquery.


Can you show me an example?

The link you provided was good, but it mostly shows parent child relationships, going from child to parent.


I have a lookup relationship going from Invoices to Orders, which i was able to SOQL.

But i need to go the other way around now, going from Orders to Invoices....There is no lookup going from Orders to how could i proceed? what do i need to do to make this work?




Probably something like


Select Id,(Select Id From Orders) From Invoices


Or vice versa, I'm not sure which way the lookup goes, but you get the idea.  The subquery (aka the child object) goes in the parentheses.