[SalesForce] SOQL: Inner query on lookup field

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?

Best Answer

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.