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:
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?
Best Answer
There's no need for a filter; the sub-query does this for you automatically. The following query should work:
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.