3

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
Vipin CP
  • 3,642
  • 3
  • 33
  • 55
  • please give sample of the expected result... – J. Zend Jul 04 '16 at 07:42
  • @J.Zend my question edited, which has expected result shown. Thanks – Vipin CP Jul 04 '16 at 07: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 Jul 04 '16 at 07: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 Jul 04 '16 at 07:58

1 Answers1

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

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • 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. – Vipin CP Jul 04 '16 at 07:53
  • @vipincp Do you still have a problem? – Tim Biegeleisen Jul 04 '16 at 09:36
  • 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 – Vipin CP Jul 04 '16 at 09:38
  • 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. – Tim Biegeleisen Jul 04 '16 at 09:44