function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
imAkashGargimAkashGarg 

inner join in SOQL

I need to add a inner join to my querry to fetch the master records based on the child records.

 

My Code:

order = [select name,id,Order_Printed__c, Order_Status__c, (select name, ActiveFlag__c,Item_Supplier__c,
                            from Order_Lines__r where (ActiveFlag__c = True AND item_Supplier__c = 'business') )
                            from Order__c where Order_Status__c = 'Ready for dispatch' AND Order_Printed__c = False];

This code fetches the order even if no matching Order_line is found. but i want only orders with matching orderlines.

 

Thanks in advance

Best Answer chosen by Admin (Salesforce Developers) 
Sam27Sam27

It can be written like 

 

                            

order = [select name,id,Order_Printed__c, Order_Status__c, (select name, ActiveFlag__c,Item_Supplier__c,
                            from Order_Lines__r where (ActiveFlag__c = True AND item_Supplier__c = 'business') )

 from Order__c where Order_Status__c = 'Ready for dispatch' AND Order_Printed__c = False and id in (select order__c

from Order_Lines__c where (ActiveFlag__c = True AND item_Supplier__c = 'business'))];

 

 

All Answers

LakshmanLakshman

Try using following code to get matched Order Line:

 

List<Order_Lines__c> orderLines= [Select Order__c from Order_Lines__c where (ActiveFlag__c = True AND item_Supplier__c = 'business')];
List<Id> ids  = new List<Id>();
for(Order_Lines__c i: orderLines)
ids.add(i.Order__c);/*I suppose your relationship name is Order in Order_Lines object*/
List<Order__c> order = [select name,id,Order_Printed__c, Order_Status__c, (select name, ActiveFlag__c,Item_Supplier__c,
 from Order_Lines__r where (ActiveFlag__c = True AND item_Supplier__c = 'business') )
 from Order__c where Order_Status__c = 'Ready for dispatch' AND Order_Printed__c = False AND Id in: ids];

 Let me know if you face any issues in it.

 

Regards,

Lakshman


pankaj.raijadepankaj.raijade

you can just loop through the result see where the line item list size is zero and neglact the record.
Sam27Sam27

It can be written like 

 

                            

order = [select name,id,Order_Printed__c, Order_Status__c, (select name, ActiveFlag__c,Item_Supplier__c,
                            from Order_Lines__r where (ActiveFlag__c = True AND item_Supplier__c = 'business') )

 from Order__c where Order_Status__c = 'Ready for dispatch' AND Order_Printed__c = False and id in (select order__c

from Order_Lines__c where (ActiveFlag__c = True AND item_Supplier__c = 'business'))];

 

 

This was selected as the best answer
LakshmanLakshman

Sam just optimized the code.

Cheers Sam :)

 

Regards,

Lakshman
pankaj.raijadepankaj.raijade

I think query in where clause wont work in salesforce. 
Sam27Sam27

Well, it (subquery in where clause in SOQL) perfectly works in salesforce, I have used it multiple times.
pankaj.raijadepankaj.raijade

Sam can you just give me exact syntax for some object 

because when I tried it didnt work.
Sam27Sam27

well yeah

 

Account[] ac = [select id, name from Account where id in (select AccountId from Contact)];

 

this one is very basic, it gives those account which have atleast one contact. You can make up on it.

 

Regards
imAkashGargimAkashGarg

Thanks Sam
pankaj.raijadepankaj.raijade

Thanks Sam,
Sam27Sam27

akash and pankaj

 

welcome fellas............