> I was surprised about the mess the forum created in my post...
Why the forum owners don't post this info where everyone can always see it, I don't know, but to format code here, use
BBCode code tags.
Before version 5.7.3, JOIN outperforms IN(SELECT...). And as RJ says, Coalesce() is way easier to read & maintain than nested IfNull()s. So ...
SELECT
Coalesce( t4.ItemID, t3.ItemID, t2.ItemID, t1.ItemID ) AS ID,
Coalescet t4.ParentItemID, t3.ParentItemID, t2.ParentItemID, t1.ParentItemID ) AS Parent,
Coalesce( tp4.ItemNo, tp3.ItemNo, tp2.ItemNo, tp1.ItemNo ) AS ItemNo
FROM ItemsParents AS t1
JOIN ItemsDB AS items ON t1.parentID = items.itemNoInt -- replace Where In(Select...)
LEFT JOIN ItemsDB AS tP1 ON t1.ItemID = tP1.ID
LEFT JOIN ItemsParents AS t2 ON t1.ItemID = t2.ParentItemID
LEFT JOIN ItemsDB AS tP2 ON t2.ItemID = tP2.ID
LEFT JOIN ItemsParents AS t3 ON t2.ItemID = t3.ParentItemID
LEFT JOIN ItemsDB AS tP3 ON t3.ItemID = tP3.ID
LEFT JOIN ItemsParents AS t4 ON t3.ItemID = t4.ParentItemID
LEFT JOIN ItemsDB AS tP4 ON t4.ItemID = tP4.ID;
It's an awfully cumbersome way to traverse a tree. And for every additional child node level, you have to add terms to the Selects and add two more Left Joins. Yikes. Are you sure you wouldn't prefer a straightforward edge list ancestor query like listing 7e at
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html ?