Re: LEFT JOIN - loosing a line from LEFT table selection

[Rick James]

A little easier to follow (if you screen is wide enough):

SELECT  IFNULL(t4.ItemID,       IFNULL(t3.ItemID,       IFNULL(t2.ItemID,       t1.ItemID))) AS ID,
        IFNULL(t4.ParentItemID, IFNULL(t3.ParentItemID, IFNULL(t2.ParentItemID, t1.ParentItemID))) as Parent,
        IFNULL(tp4.ItemNo,      IFNULL(tp3.ItemNo,      IFNULL(tp2.ItemNo,      tp1.ItemNo))) AS ItemNo
    FROM       ItemsParents AS t1                                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
    WHERE  t1.ParentItemID = 
      ( SELECT  ID  FROM  ItemsDB  WHERE  ItemNoInt = 359 )

Consider using COALESCE() instead of lots of IFNULL()s.

My head is spinning! Could you explain what you are trying to do -- go UP a hierarchy? Or DOWN? Why are there two tables? Why is the alias for ...Parents t#, not tP#?