Something like

SELECT lvl1.iid AS n1,
lvl2.iid AS n2,
lvl3.iid AS n3,
lvl4.iid AS n4,
lvl5.iid AS n5,
lvl6.iid AS n6,
lvl7.iid AS n7,
lvl8.iid AS n8,
lvl9.iid AS n9,
lvl10.iid AS n10,
lvl10.iID,
coalesce(lvl10.cName, lvl9.cName, lvl8.cName, lvl7.cName, lvl6.cName,
lvl5.cName, lvl4.cName, lvl3.cName, lvl2.cName, lvl1.cName) as cName
FROM mAccounts lvl1
LEFT JOIN mAccounts AS lvl2 ON lvl1.iid = lvl2.ipid
LEFT JOIN mAccounts AS lvl3 ON lvl2.iid = lvl3.ipid
LEFT JOIN mAccounts AS lvl4 ON lvl3.iid = lvl4.ipid
LEFT JOIN mAccounts AS lvl5 ON lvl4.iid = lvl5.ipid
LEFT JOIN mAccounts AS lvl6 ON lvl5.iid = lvl6.ipid
LEFT JOIN mAccounts AS lvl7 ON lvl6.iid = lvl7.ipid
LEFT JOIN mAccounts AS lvl8 ON lvl7.iid = lvl8.ipid
LEFT JOIN mAccounts AS lvl9 ON lvl8.iid = lvl9.ipid
LEFT JOIN mAccounts AS lvl10 ON lvl9.iid = lvl10.ipid
WHERE lvl1.ipid = 0

By the way, if you're using Firebird 2.1, recursive CTEs are normally a more 'beautiful' solution to recursive problems.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Bhavbhuti Nathwani
Sent: 30. juni 2009 14:04
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Leaf Node fields in this self-join statment

Hi all

The following SQL will give me 10 levels of self join. Now when I add the cName and iID columns, I am expecting to have these details from the last most filled in nn column.

Please advise
Thanks and regards
Bhavbhuti

SELECT lvl1.iid AS n1,
lvl2.iid AS n2,
lvl3.iid AS n3,
lvl4.iid AS n4,
lvl5.iid AS n5,
lvl6.iid AS n6,
lvl7.iid AS n7,
lvl8.iid AS n8,
lvl9.iid AS n9,
lvl10.iid AS n10,
lvl10.iID,
lvl10.cName
FROM mAccounts lvl1
LEFT JOIN mAccounts AS lvl2 ON lvl1.iid = lvl2.ipid
LEFT JOIN mAccounts AS lvl3 ON lvl2.iid = lvl3.ipid
LEFT JOIN mAccounts AS lvl4 ON lvl3.iid = lvl4.ipid
LEFT JOIN mAccounts AS lvl5 ON lvl4.iid = lvl5.ipid
LEFT JOIN mAccounts AS lvl6 ON lvl5.iid = lvl6.ipid
LEFT JOIN mAccounts AS lvl7 ON lvl6.iid = lvl7.ipid
LEFT JOIN mAccounts AS lvl8 ON lvl7.iid = lvl8.ipid
LEFT JOIN mAccounts AS lvl9 ON lvl8.iid = lvl9.ipid
LEFT JOIN mAccounts AS lvl10 ON lvl9.iid = lvl10.ipid
WHERE lvl1.ipid = 0