MySQL parent children one query selection

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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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

Method 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.

Method 3

Here is another option to get the results you are looking for. You can group the parent and children together by the parent’s id and then indicate which record IS the parent (isparent) … like so: (this example assumes that a parent record has a NULL value for parent.. instead of 0.. but it can work with 0 too)

SELECT
    IFNULL(parent,id) AS parentid,
    IF(parent IS NULL,1,0) AS isparent,
    name
FROM my_table
ORDER BY
    parentid ASC, <- groups/orders all parentid's together
    isparent DESC, <- orders the parent first
    id DESC <- orders children by their id next

The rest should be easy to figure out, grouping your output, etc.

Method 4

You can use the below query with UNION:

SELECT ID, NAME, PARENT
FROM MY_TABLE AS A,
     MY_TABLE AS B
WHERE A.PARENT != 0 AND
      A.PARENT = B.ID
UNION
SELECT ID, NAME, PARENT
FROM MY_TABLE
WHERE PARENT == 0

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0