I have one parent "quote__c" and child object "Quote_line_item__c" .I want get the parent id

with query some thing like this

select id,(select name from quote_line_item__c where name='123' and name='456') from quote__c

I know this is wrong .i need to query like this how i can do it?

I need to get the id which only two child objects '123' and '456' exactly .in case it has three items'123','456','789' it do not show.

  • Have you tried using IN clause? i.e. SELECT Name FROM quote_line_item__c where name IN requireNameList ?
    – highfive
    Jul 21 '14 at 7:38
  • @highfive IN will be satisfied when at least 1 matches, this sounds like all items from the IN have to be present - and only them. IN is just a shortcut to not write Name = 123 OR Name = 456 OR ...
    – eyescream
    Jul 21 '14 at 8:06
  • Yes @eyescream got it and agreed. It'll not helpful here. :)
    – highfive
    Jul 21 '14 at 8:17

Tricky one! Do you have a rollup summary field that holds count of line items? If you do - this might be one way to do it:

FROM Quote__c
WHERE Id IN (SELECT Quote__c FROM Quote_Line_Item__c WHERE Name = '123')
AND Id IN (SELECT Quote__c FROM Quote_Line_Item__c WHERE Name = '456')
AND Lines_count__c = 2

How about using Having ??

SELECT Quote__c, Count(Quote__c)  
  FROM Quote_Line_Item__c
 WHERE Name = '123' or Name ='456'
 GROUP By Quote__c
HAVING Count(quote__c) = 2
  • Interesting one! Might burn a lot of query rows though if "popular" name is used...
    – eyescream
    Jul 21 '14 at 9:44
  • I would expect that it only counts the ones matching the having in SoQL query rows, or am I wrong ?
    – ManSpan
    Jul 21 '14 at 10:13
  • 1
    In regular SQL HAVING is a "WHERE-like" second pass on the query results, you do have all the rows and aggregations and you filter them out a bit more. It would be best to experiment but I'd assume the rows would be considered already wasted for the purposes of 50K limit.
    – eyescream
    Jul 21 '14 at 10:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.