Is this possible in SQL?

Question

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?

Answer 1

this is a good resource for what you're talking about, particularly if you're working with a database system that doesn't have recursive query support. It discusses how you'll need to structure your data in order to do what you want to more easily.

Answer 2

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.

Answer 3

Yeah, it i possible in SQL. You can use recursive queries for that purpose. Take a look at here. Read the full page.

WITH req_query AS 
(
  SELECT  id, 
          name,
          ancestorid
  FROM    your_table
  WHERE   name='homo sapiens'    //If you want to search by id, then change this line

  UNION

  SELECT  yt.id, 
          yt.name,
          yt.ancestorid
  FROM    your_table yt,
          req_query rq
  WHERE   yt.id = rq.ancestorid
          AND rq.id != 0
          AND rq.id is not null
)

SELECT  * 
FROM    req_query
;

Edit This will work with ORACLE, SQL Server, PostgreSQL and with any other database that has recursive query support. To deal with the databases that don't support recursive queries, you will have to use the nested set model.

Answer 4

Yes this is easily possible in SQL. In MS SQL server you can do this using the hierachyid data type. You can traverse up or down the tree with ease and with low overhead. I would prefer the hierarchyid over recursion if you don't move your trees around. If you do plan to move trees arond the recursion will be your best bet.

Answer 5

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 

Answer 6

You most likely don't want to make your first specie have an ID of null. null is very different from 0. null means you don't know what the value is. 0 means the value is 0. Note that null <> null and 0 = 0. This can affect how you search for an recognize the firt specie.