MySQL parent children one query selection

Question

I have a MySQL table with fields as below:

id            name             parent
1             Fruit             0
2             Meat              0
3             Orange            1
4             Beef              2

where parent field means the upper level id. For example Fruit id is 1, and Orange is one of the fruit so the parent is 1.

However I want to do an efficient MySQL query to fetch all records in the format parent->children->parent->children format. How can I do that?

The result record of the query should look like:

id            name             parent
1             Fruit             0
3             Orange            1
2             Meat              0
4             Beef              2

Answer 1

You need a recursive join which mysql doesn't support. The only thing you can do is determine the maximum level of depth (i your case it 1 since you have p->c) and with this you can determine the number of joins needed :

maximum level of depth = number of self-joins:

SELECT
    p.id as parent_id,
    p.name as parent_id,
    c1.id as child_id,
    c1.name as child_name
FROM 
    my_table p
LEFT JOIN my_table c1
    ON c1.parent = p.id
WHERE
    p.parent=0

For example if you max level of depth was 3 the you would need 3 self-joins:

SELECT
    p.id as parent_id,
    p.name as parent_id,
    c1.id as child_id_1,
    c1.name as child_name_1,
    c2.id as child_id_2,
    c2.name as child_name_2,
    c3.id as child_id_3,
    c3.name as child_name_3
FROM 
    my_table p
LEFT JOIN my_table c1
    ON c1.parent = p.id
LEFT JOIN my_table c2
    ON c2.parent = c1.id
LEFT JOIN my_table c3
    ON c3.parent = c2.id
WHERE
    p.parent=0

Answer 2

How about this?

select * from foods
order by (case parent when 0 then id*1000 else parent*1000+id end), id

It's not very nice, as it won't work if you have more than 1000 foods with the same parent, but if you know that limit, it should do the trick.