Let s say I have a table called species with 3 columns: Id , ancestorId and name . The ancestorId is the Id of the ancestor specie, so for example if the ancestor of the homo sapiens is the australopithecus, and australopithecus s Id is 5, then homo sapiens s ancestorId is 5. Let s say, also, that there is a specie called First specie whose Id is either 0 or null. So what I want is to select a list of ancestors from a particular specie, say, homo sapiens, to the First specie , no matter how many nodes are on the way. Is this possible with SQL?
Hierarchical data - Is this possible in SQL
De openkb
Sommaire |
Questions
Answers
The ANSI way is to use a recursive WITH clause:
WITH hierarchy AS ( SELECT t.id, t.name, t.ancestor FROM TABLE t WHERE t.ancestor IS NULL UNION SELECT t.id, t.name, t.ancestor FROM TABLE t JOIN hierarchy h ON h.ancestorid = t.id) SELECT * FROM hierarchy
Supported by:
- SQL Server 2005+
- Oracle 11gR2
- PostgreSQL 8.4+
Oracle s had hierarchical query support since v2, using CONNECT BY syntax.
Source
License : cc by-sa 3.0
http://stackoverflow.com/questions/3508762/is-this-possible-in-sql