Hi Svein

I am sorry for not being able to explain the problem better. I will try again with actual examples:

SELECT lvl0.iid AS n0,
lvl0.cDesc AS cDesc,
lvl0.iID AS iID
FROM mItems lvl0
WHERE lvl0.ipid = 0
returns one record: 8, Inventory, 8
this is correct as it is the top most in the selfjoin


SELECT lvl0.iid AS n0,
lvl1.iid AS n1,
COALESCE(lvl1.cDesc, lvl0.cDesc) AS cDesc,
COALESCE(lvl1.iID, lvl0.iID) AS iID
FROM mItems lvl0
LEFT JOIN mItems lvl1 ON lvl1.ipid = lvl0.iid
WHERE lvl0.ipid = 0
returns 7 records instead of 8 (the above Inventory, 8 record is missing)
8, 9, Raw Material, 9
8, 10, Semi-Finished, 10
...
8, 15, Trading Goods, 15


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
returns 9 records, record from 1st and many from 2nd SQL are missing
8, 9, 54, rm1, 54 -- child of Raw Material, 9
8, 9, 55, rm2, 55 -- child of Raw Material, 9
8, 10, null, Semi-Finished, 10 -- as it does not have child-records
...
The record for Raw Material, 9 is missing because it's children appear in the recordset, same as missing Inventory, 8 in second SQL
But as Semi-Finished does not have any child records, it appears in the recordset.

Hope this explains what I have noticed.

Thanks for your response.
Kind regards
Bhavbhuti

--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer <svein.erling.tysvaer@...> wrote:
>...