Are you saying that n0, n1 etc. disappears or that cDesc or iID
disappears? If you're saying that n0 disappears, then I have no clue
what has happened. If it is cDesc that changes value, then my guess is
that lvl2.cDesc is blank rather than null. COALESCE takes the first
non-null value amongst the fields you specify, and blank is not the same
as NULL.

Another thing is that with LEFT JOIN some criteria may have to be in the
LEFT JOIN clause and not in the WHERE clause, but your WHERE clause
seems very simple.

Set

Bhavbhuti Nathwani wrote:

> 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