Selecting records in order of parent id

Question

Simple question.. just can't get the result set in the order I need :p

I have a table "categories"

id    | name     | parent
1       apple      0
2       macintosh  1
3       atari      0
4       st         3
5       lisa       1

I'm trying to select get the following result set:

1   apple      0
5   lisa       1
2   macintosh  1
3   atari      0
4   st         3

So in other words I want all columns of all rows, where rows with parents are immediately after their parent row and all are sorted alphabetically.

parent a
 child a
 child b
parent b
 child a

The query I'm using now doesn't correctly re-order the rows after their parents

SELECT a.*, b.* FROM categories a RIGHT JOIN categories b ON b.parent = a.id

Answer 1

If those with no parents had null in their parent column, your statement would be very simple:

SELECT id, name, parent FROM categories order by coalesce(parent, id), id;

If you insist on 0 representing no parent, you can use more verbose CASE WHEN ... THEN ... statement.

Edit:

-- Sorting by name instead
select a.id, a.name, a.parent 
from categories a left join categories b on a.parent=b.id 
order by coalesce(b.name, a.name), a.name

Answer 2

For a simple, perhaps suboptimally-scalable solution, I recommend hard-coding this with the maximum number of levels you will have:

For 2 levels only:

SELECT p2.name as `Parent name`, p1.*
FROM categories p1
LEFT JOIN categories p2 on p1.categories_id = p2.id

You're really asking about sorting, so I'd recommend generating a "path"-like string: (see below for sample output of this query)

SELECT Concat(If(isnull(p2.name),"",Concat("/",p2.name)),"/",p1.name) as `generated path`, p2.name as `Parent name`, p1.*
FROM categories p1
LEFT JOIN categories p2 on p1.parent_id = p2.id
order by `generated path`

For 3 levels, though your data doesn't have this yet -- path omitted because it will get ugly :)

SELECT p3.name as `Grandparent name`, p2.name as `Parent name`, p1.*
FROM categories p1
LEFT JOIN categories p2 on p1.categories_id = p2.id
LEFT JOIN categories p3 on p2.categories_id = p3.id

A more comprehensive solution for quickly selecting all items in a particular category at any level, which does require some work on all writes, is implementing a 'right' and 'left' numbering concept. But, further discussion on that is almost certainly going beyond the scope of what you're asking. However, that's the only good way in my experience to make this kind of self-referencing table very useful if it's going to get big (maybe after 1000+ rows with 3 to 10 levels).

Addendum: sample output from the second query:

generated path         Parent name         id         name         parent_id
----------------------------------------------------------------------------
/apple                                      1         apple                0
/apple/lisa                  apple          5         lisa                 1
/apple/mac                   apple          2         mac                  1
/atari                                      3         atari                0
/atari/st                    atari          4         st                   3