Hierarchical SQL Queries: Best SQL query to obtain the whole branch of a tree from a [nodeid, parentid] pairs table given the end node id

Question

Is there any way to send a recursive query in SQL?

Given the end node id, I need all the rows up to the root node (which has parentid = NULL) ordered by level. E.g. if I have something like:

nodeid | parentid
a      | NULL    
b      | a       
c      | b

after querying for end_node_id = c, I'd get something like:

nodeid | parentid | depth
a      | NULL     | 0
b      | a        | 1
c      | b        | 2

(Instead of the depth I can also work with the distance to the given end node)

The only (and obvious) way I could come up with is doing a single query per row until I reach the parent node.

Is there a more efficient way of doing it?

it has to work for Oracle and Postgres (although a generic solution would be ideal) — NotGaeL, Jan 09 '15 at 09:35
`WITH RECURSIVE` (http://www.postgresql.org/docs/8.4/static/queries-with.html) seems to work fine for Postgres. Now I need a solution for Oracle — NotGaeL, Jan 09 '15 at 09:37

score 5 · Answer 1 · answered Jan 09 '15 at 09:06

If you are using mssql 2005+ you can do this:

Test data:

DECLARE @tbl TABLE(nodeId VARCHAR(10),parentid VARCHAR(10))

INSERT INTO @tbl
VALUES ('a',null),('b','a'),('c','b')

Query

;WITH CTE
AS
(
    SELECT
        tbl.nodeId,
        tbl.parentid,
        0 AS Depth
    FROM
        @tbl as tbl
    WHERE
        tbl.parentid IS NULL
    UNION ALL
    SELECT
        tbl.nodeId,
        tbl.parentid,
        CTE.Depth+1 AS Depth
    FROM
        @tbl AS tbl
        JOIN CTE
            ON tbl.parentid=CTE.nodeId
)
SELECT
    *
FROM
    CTE

This has the same problem as the accepted solution: I want to provide an end node id to get the branch not the root node id, but it is a simple change to get it that way so I consider it a valid answer too. — NotGaeL, Jan 09 '15 at 12:27

NotGaeL · Accepted Answer · 2015-01-11T14:59:44.387

Ended up with the following solutions (where level is the distance to the end node)

Oracle, using hierarchical queries (thanks to the info provided by @Mureinik):

SELECT     IDCATEGORY, IDPARENTCATEGORY, LEVEL
FROM       TNODES
START WITH IDCATEGORY=122
CONNECT BY IDCATEGORY = PRIOR IDPARENTCATEGORY;

Example using a view so it boils down to a single standard SQL query (requires >= 10g):

CREATE OR REPLACE VIEW VNODES AS 
SELECT CONNECT_BY_ROOT IDCATEGORY "IDBRANCH", IDCATEGORY, IDPARENTCATEGORY, LEVEL AS LVL
FROM TNODES 
CONNECT BY IDCATEGORY = PRIOR IDPARENTCATEGORY;

SELECT * FROM VNODES WHERE IDBRANCH = 122 ORDER BY LVL ASC;

http://sqlfiddle.com/#!4/18ba80/3

Postgres >= 8.4, using a WITH RECURSIVE Common Table Expression query:

WITH RECURSIVE BRANCH(IDPARENTCATEGORY, IDCATEGORY, LEVEL) AS (
    SELECT IDPARENTCATEGORY, IDCATEGORY, 1 AS LEVEL FROM TNODES WHERE IDCATEGORY = 122
  UNION ALL
    SELECT p.IDPARENTCATEGORY, p.IDCATEGORY, LEVEL+1
    FROM BRANCH pr, TNODES p
    WHERE p.IDCATEGORY = pr.IDPARENTCATEGORY
  )
SELECT IDCATEGORY,IDPARENTCATEGORY, LEVEL
FROM BRANCH
ORDER BY LEVEL ASC

Example using a view so it boils down to a single standard SQL query:

CREATE OR REPLACE VIEW VNODES AS 
WITH RECURSIVE BRANCH(IDBRANCH,IDPARENTCATEGORY,IDCATEGORY,LVL) AS (
  SELECT IDCATEGORY AS IDBRANCH, IDPARENTCATEGORY, IDCATEGORY, 1 AS LVL FROM TNODES
  UNION ALL
    SELECT pr.IDBRANCH, p.IDPARENTCATEGORY, p.IDCATEGORY, LVL+1
    FROM BRANCH pr, TNODES p
    WHERE p.IDCATEGORY = pr.IDPARENTCATEGORY
  )
SELECT IDBRANCH, IDCATEGORY, IDPARENTCATEGORY, LVL
FROM BRANCH;

SELECT * FROM VNODES WHERE IDBRANCH = 122 ORDER BY LVL ASC;

http://sqlfiddle.com/#!11/42870/2

score 2 · Answer 3 · answered Jan 09 '15 at 09:50

2

For Oracle, as requested in the comments, you can use the connect by operator to produce the hierarchy, and the level pseudocolumn to get the depth:

SELECT     nodeid, parentid, LEVEL
FROM       t
START WITH parentid IS NULL
CONNECT BY parentid = PRIOR nodeid;

answered Jan 09 '15 at 09:50

Mureinik

297,002
52
306
350

I've marked it as accepted because START WITH / CONNECT BY is exactly what I needed for the Oracle solution, although the actual query differs a little bit (the end node id is the node id provided, not the root node id). Check my answer for the actual solution. – NotGaeL Jan 09 '15 at 12:14
(sorry I unaccepted your answer, you showed me exactly what I needed to get to the solution I wanted in Oracle but I felt I had to provide a more detailed answer showing how to use views both in oracle and postgresql to reduce the problem to a single simple standard SQL select query) – NotGaeL Jan 11 '15 at 14:55

Hierarchical SQL Queries: Best SQL query to obtain the whole branch of a tree from a [nodeid, parentid] pairs table given the end node id

3 Answers3