Hierarchical data - Is this possible in SQL

De openkb
Aller à : Navigation, rechercher

Sommaire

Questions

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?

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

Related

Outils personnels
Espaces de noms

Variantes
Actions
Navigation
Outils