CODEWITHSUNDEEP
sqlhierarchical-data
Juan
Juan

Reputation: 15715

Is this possible in SQL?

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?

Upvotes: 1

Views: 173

Answers (5)

sasfrog
sasfrog

Reputation: 2460

Managing Hierarchical Data in MySQL 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.

Upvotes: 3

Tom Groszko
Tom Groszko

Reputation: 1

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.

Upvotes: 0

user423575
user423575

Reputation: 11

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

Upvotes: 0

MD Sayem Ahmed
MD Sayem Ahmed

Reputation: 29166

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.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332591

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.

Upvotes: 3

Related Questions