Thanks a Lot! That Worked. regards Akshay
On Wed, Aug 29, 2012 at 12:58 AM, Brian <[email protected]> wrote: > See the section in the documentation labeled "Recursive Queries" just > under the UUID anchor: > http://www.h2database.com/html/advanced.html#uuid > > (Comitters: Looks like the recursive queries section in advanced.html > isn't anchored/indexed at the top) > > You'll have to be very careful. For example: 109 didn't have any children > in the example data you provided. > Also - if there are loops in your DATA (101's parent is 101? - that's bad > loop!) - then you'll DOS yourself. > > WITH LINK(GROUP_ID, GROUP_NAME, LEVEL) AS ( > SELECT GROUP_ID, GROUP_NAME, 1 FROM ACCOUNT_GROUP WHERE PARENT=101 AND > GROUP_ID <> PARENT > UNION ALL > SELECT ACCOUNT_GROUP.GROUP_ID, IFNULL(LINK.GROUP_NAME || '/', '') || > ACCOUNT_GROUP.GROUP_NAME, LEVEL + 1 > FROM LINK INNER JOIN ACCOUNT_GROUP ON LINK.GROUP_ID = > ACCOUNT_GROUP.PARENT > ) > SELECT GROUP_ID,GROUP_NAME,LEVEL FROM LINK WHERE GROUP_NAME IS NOT NULL > ORDER BY GROUP_ID; > > -Brian > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/h2-database/-/zGZcOUQ_JFEJ. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > -- Akshay Mehta Ph. - 09302104522 -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
