Hi all

SELECT lvl0.iid AS n0,
lvl1.iid AS n1,
lvl2.iid AS n2,
COALESCE(lvl2.cDesc, lvl1.cDesc, lvl0.cDesc) AS cDesc,
COALESCE(lvl2.iID, lvl1.iID, lvl0.iID) AS iID
FROM mItems lvl0
LEFT JOIN mItems lvl1 ON lvl1.ipid = lvl0.iid
LEFT JOIN mItems lvl2 ON lvl2.ipid = lvl1.iid
WHERE lvl0.ipid = 0

I am going through this query one section at a time. What I have noticed happening is that records that have children get excluded from the returned dataset as soon as I add a level. Thus when I had the simplest of this SQL wtih on lvl0 I was seeing the top most parent record and only one record. Added lvl1 and I saw the children of this originakl one record but the original record was gone from the data set and so on. I have tried all joins I know about INNER, LEFT / RIGHT / FULL OUTER but no joy.

Please advise how to include those records too.

Thanks and regards.
Bhavbhuti