2

I have a table which has self lookup for the field name parent_id. I want to get the product code for the corresponding parent_id.

please let me know whats wrong with this query:

SELECT p.id,p.Name,p.productcode,

(select productcode from Products__r where Parent__id=p.id limit 1)

FROM product p

it works fine if I hardcode the where clause id, for exmaple

SELECT p.id,p.Name,p.productcode,

(select productcode from Products__r where Parent_id='1' limit 1)

FROM product p 

Salesforce Table:

enter image description here

SQL for the above results will be like this :

Select id, Name, product_code, (select product_code from product b where b.parent_id=a. id) as products__r from product a

Can someone please convert the above SQL to SOQL?

  • Do you want to get all child records of Product p? – Tushar Sharma Jan 22 '18 at 8:59
  • please check the required results above – mani Jan 23 '18 at 17:55
  • Is sfdcfox's answer doesn't work for you? Your question is still not much clear. – Tushar Sharma Jan 24 '18 at 3:35
  • No sfdcfox's answer is not working for me. I have both child and parent records in a table. there is a self lookup field called parent_id which stores the parent _id for a child record. if its a parent record then the parent_id is 0, for a child record it stores the id of the parent . Now in my query I want to know the product code of the parent code (see the results above) . In SQL it can be done like this - Select a.id, a.Name, a.product_code, (select product_code from product b where b.parent_id=a. id) as products__r from producct a – mani Jan 24 '18 at 17:48
  • Check the more details provided in question. Table Values and SQL – mani Jan 24 '18 at 18:09
4

There's no need for a filter; the sub-query does this for you automatically. The following query should work:

SELECT Name, productcode,
(select productcode from Products__r limit 1)
FROM product2

Note that you can't currently compare fields to each other in SOQL, there's a few Ideas out there for that, but it will likely take a while to see this become available.

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.