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
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?