how to query with child relations to same table and order this correctly

Question

Hi,

Take this table:

id      name      sub_id
---------------------------
1        A        (null)
2        B        (null)
3        A2       1
4        A3       1

The sub_id column is a relation to his own table, to column ID.

 subid --- 0:1  --- id

Now I have the problem to make a correctly SELECT query to show that the child rows (which sub_id is not null) directly selected under his parent row. So this must be a correctly order:

1    A    (null)
3    A2   1
4    A3   1
2    B    (null)

A normal SELECT order the id. But how or which keyword help me to order this correctly?

JOIN isn't possible I think because I want to get all the rows separated. Because the rows will be displayed on a Gridview (ASP.Net) with EntityDataSource but the child rows must be displayed directly under his parent.

Thank you.

Answer 1

select * from table1 order by name, sub_id will in this case return your desired result but only because the parents names and the child name are similar. If you're using SQL 2005 a recursive CTE will work:

WITH recurse (id, Name, childID, Depth)
AS
(
    SELECT id, Name, ISNULL(childID, id) as id, 0 AS Depth
    FROM table1 where childid is null
    UNION ALL
    SELECT table1.id, table1.Name, table1.childID, recurse.Depth + 1 AS Depth FROM table1
    JOIN recurse ON table1.childid = recurse.id
)

SELECT * FROM recurse order by childid, depth

Answer 2

Since recursion is an expensive operation because basicly you're firing multiple queries to your database you could consider using the Nested Set Model. In short you're assigning numbers to ranges in your table. It's a long article but it worth reading it. I've used it during my internship as a solution not to have 1000+ queries ;) But bring it down to 1 query.

You're handling 'overhead' now lies at the point of updating the table by adding, updating or deleting records. Since you then have to update all the records with a bigger 'right-value'. But when you're retrieving the data, it all goes with 1 query :)

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Answer 3

SELECT * FROM table ORDER BY COALESCE(id,sub_id), id

btw, this will work only for one level.. any thing more than that requires recursive/cte function