I am appending the Description Field of a MysQl Table with the Names and Values of various related Lookup Tables (LUTs). Not all records have any/all Look-up table values, a problem I have solved with a lot of great help here on Stackoverflow.
My challenge now is that the values on the Look-up tables can have parents and that can in some cases go up a few levels (using a field ParentID in each LUT).
I can extract the Parents to one level up using brute force additional joins as in this SqlFiddle, yet adding multiple joins for each LUT to get possible Parents seems unwieldy. Is there a way to modify the query to 'recursively' grab the parents.
As you can see in the case of e.g. Plantain my query finds Banana but won't find Fruit unless I do yet another join, which means I'd need 3+ joins per table, and this query will have a dozen or more joined tables when all is said and done.
