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?

6
  • Do you want to get all child records of Product p? Jan 22, 2018 at 8:59
  • please check the required results above
    – mani
    Jan 23, 2018 at 17:55
  • Is sfdcfox's answer doesn't work for you? Your question is still not much clear. Jan 24, 2018 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, 2018 at 17:48
  • Check the more details provided in question. Table Values and SQL
    – mani
    Jan 24, 2018 at 18:09

1 Answer 1

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.

3

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .