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
Shreya Mangade 8Shreya Mangade 8 

Querying CustomObject (related to Product2) and OpportunityLineItem

In my application there is  a custom object related to the Product2. What I need is to fetch some fields of OpportunityLineItem  and  some fields of custom object which is related to Product2. I am executing the following queries : 
1. SELECT ID from RelatedCustomObject where Product2.Id IN (SELECT ProductID from OpportunityLineItem where OpportunityID = '123')
2. SELECT quantity from  OpportunityLineItem where OpportunityID = '123'

But in the scenerio where OpportunityLineItem has 7 records, out of which 2 uses same Product , the first query is eliminating the duplicate ones due to IN clause and giving results for the unique ones.But I need to fetch them all.Could anyone please suggest what can be done in this case?User-added image 
Ravneet Singh 44Ravneet Singh 44
Hi 

I have similar issue in my dev org where IN clause is taking duplicate values as a single value. Is there any way to get duplicate values in a nested query using IN clause or any other way possible?
Daniel GrabowskiDaniel Grabowski
Hmm you could switch it up a bit...

SELECT Id, ( SELECT Id FROM RelatedCustomObject__r) FROM Product2 where Id IN (SELECT ProductID from OpportunityLineItem where OpportunityID = '123')

Good Luck! 
Suraj Tripathi 47Suraj Tripathi 47
Hi Shreya Mangade,
For your first query issue, you can fetch the RelatedCustomObject__r as an inner query from products.

Refer to the code below-
 
SELECT Id, ( SELECT Id FROM RelatedCustomObject__r) FROM Product2 where Id IN (SELECT ProductID from OpportunityLineItem where OpportunityID = '123')

In case you find any other issue please mention. 
If you find your Solution then mark this as the best answer. 
Thanks and Regards
Suraj Tripathi.