You're right, Bhavbhuti, CTEs can only help getting cName of your original query, it will not help getting the ten iid fields on the same line (or rather, I do not know how to do that) in ten different fields.

Set

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

Hi Svein

Thanks a lot for the suggestion. I have converted even the second last column using COALESCE to get to the required details.

Yes, I am using FB21. Just like the original SQL below (if I remember correct), which I have modified, you have also helped me create the recursive CTE and I have been able to utilize that. Now how will the same recursive CTE give me multiple columns as the first 10 fields I am at a loss.

Thanks and regards.
Bhavbhuti

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> 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
>




------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links