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

Question

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

Look at Managing Hierarchical Data in MySQL.

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.

Your 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 :)