Re: nested lookup - query to get the respective descendents

[Rick James]

You are looking for not just the immediate children, but all descendents, correct? (I changed the Subject.)

Sorry, there is no SQL syntax for such.

Plan A: You have a loop in your code to descend until there are no more children.

Plan B: Do similar stuff in a Stored Procedure.

The code would probably be building a longer and longer list of PKs. This list would be put into a IN(...) clause.

You would start with $list = the PK of the top node. Then get a new value for $list from something like

SELECT  GROUP_CONCAT(PK ORDER BY PK SEPARATED BY ', ')
    FROM  area_lookup
    WHERE  parent IN ($list)

When the value stops changing, break out of the loop.