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.
