Sql question: Getting Parent rows followed by child rows

Question

id parent_id

1 0

2 0

3 2

4 0

5 1

6 0

I need a query that will return parent rows (parent_id=0) followed by its child rows

first parent

all children of first parent

second parent

all children of second parent

third parent

fourth parent

Expected result: ordered by id

id parent_id

1 0 (first parent)

5 1 (all children of first parent)

2 0 second parent

3 2 (all children of second parent)

4 0 third parent

6 0 fourth parent

I can use union of parents followed by all childs But that gives me parents first then the children. I need parent and immediately its children.

Anyone can help?

Answer 1

As far as i know, you can't do this with a single SQL statement if all you're storing is the parent id. If you need to retrieve the tree of data quickly, you'll have to consider storing a pre-order traversal. It's easier than it sounds and it's very well described here: http://articles.sitepoint.com/article/hierarchical-data-database

Answer 2

Here is an example solution using a union with an order by clause (It wont work for deep nesting though).

SELECT  p.id, 
        p.parent_id, 
        p.name,  
        p.id AS sequence
FROM topics AS p 
WHERE p.parent_id = 0
UNION 
SELECT  t.id, 
        t.parent_id, 
        t.name, 
        t.parent_id AS sequence
FROM topics AS t
WHERE t.parent_id <> 0
ORDER BY sequence, parent_id, name

Answer 3

You'd use a recursive CTE for this:

WITH r AS(SELECT id, NULL AS parent_id, CAST(right('000' + CAST(row_number() OVER (table.id) AS varchar), 3) AS varchar(50)) FROM table WHERE parent IS NULL
          UNION ALL
          SELECT table.id, table.parent_id, CAST(r.ord + right('000' + CAST(row_number() OVER (ORDER BY table.id) AS varchar), 3) AS varchar(50))
            FROM r JOIN table ON table.parent = r.id)
SELECT id FROM r
 ORDER BY left(ord + '000000000000000000000000000000000', 36)

Note that this particular version will break if any ID has a value greater than 999, and it will break if you have more than 12 levels. If this is a concern to you, you need to adjust the number of zeroes in the various places.

There might be better ways, but this one works.