+ Start a Discussion
mromani1mromani1 

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 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?

 

--------------------------------------------------------------------------------------------------------

 

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....

 

werewolfwerewolf

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

mromani1mromani1

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 Invoices...so how could i proceed? what do i need to do to make this work?

 

 

werewolfwerewolf

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.