Print parent hierarchy from same table in mysql database

Question

I have a table category which has fields and values as shown below in MYSQL database.

id  name   parent    sort_order
1   Men    null       0
2   Women  null       1
3   shirt   1         0
4   salwar  2          1

Here parent is a foreign key points to the same table. In my category list page i want to print the parent hierarchy(if any) along with the category name. can i do this in a single query. I have tried with group_concat in mysql but not able to generate the required result.

Expected Result:

1   men
2.  women
3.  men>shirt
4.  women> salwar

@J.Zend my question edited, which has expected result shown. Thanks — Vipin CP
– Vipin CP, Commented Jul 4, 2016 at 7:43
use the case stuff ,,,,select concat(table1.name,case when table2.parent is not null then concat('>', table1.name) else '' end) from table1 left join table2 on tabl1.id = table2.parent — J. Zend
– J. Zend, Commented Jul 4, 2016 at 7:50
For two levels (parent - child) can be done with a single query as responses show. If categories have more levels (grandfather - father - son, or more) you need recursion, with stored procedure or function. — José M. Carnero
– José M. Carnero, Commented Jul 4, 2016 at 7:58

Tim Biegeleisen · Accepted Answer · 2016-07-04 08:01:57Z

1

You can use a self-join:

SELECT *
FROM
(
    SELECT name, 1 AS rank, sort_order
    FROM category
    WHERE parent IS NULL
    UNION ALL
    SELECT CONCAT(c1.name,
        CASE WHEN c2.parent IS NOT NULL THEN CONCAT('>', c2.name) ELSE '' END), 0 AS rank, c1.sort_order
    FROM category c1 LEFT JOIN category c2
        ON c1.id = c2.parent
    WHERE c1.parent IS NULL
) t
ORDER BY t.rank DESC, t.sort_order

SQLFiddle

edited Jul 4, 2016 at 8:01

answered Jul 4, 2016 at 7:45

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Vipin CP Over a year ago

its coming like men >shirt, women>salwar, shirt , salwar. BUt this is not the expected result. I need category without parent to be printed alone. Men and women doesn't have parent and that should be printed without hierarchy. But thanks for the help. I hope i can correct this.

Tim Biegeleisen Over a year ago

@vipincp Do you still have a problem?

Vipin CP Over a year ago

YES tim.. what if i have one more level... like test category under salwar. salwar in turn child of women.. There result should come as women>salwar>test

Tim Biegeleisen Over a year ago

What you're asking for then, in general, is a recursive query, which can arbitrarily dive as many levels deep as there exists. This is non trivial, and your original question did not mention this.

Collectives™ on Stack Overflow

Print parent hierarchy from same table in mysql database

1 Answer 1

SQLFiddle

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related