0

In MySql I have category table with fields:

  • cat_id (int)
  • cat_parent_id (int)
  • cat_name (string)

How to create query that return list off all categoryes with coresponded level number

cat parent_id for root nodes is 0

sample SQL Fiddle with sample data : http://sqlfiddle.com/#!9/ba439ae/3

I have MySql version : 5.7.19 and Fiddle is on 5.6

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Jernej Pirc
  • 504
  • 1
  • 4
  • 13
  • See also many other questions with tag [tag:hierarchical-data]. – Bill Karwin Apr 16 '18 at 06:57
  • @BillKarwin for me this is a totally different question. They looking for breadcrumbs I need result table with levels for some export... With knowledge of duplicated question, i maybe can create a solution for my case but it is not a duplicate. – Jernej Pirc Apr 16 '18 at 11:58
  • I've reversed my vote for this to be a duplicate. But I suggest you to read a variety of the hierarchical-data questions. They may not be exactly the same as your query, but the principle is the same—query all descendants in a hierarchy. – Bill Karwin Apr 16 '18 at 15:01

1 Answers1

1

Try this:

WITH cte AS
(
  SELECT 
    cat_id, 
    cat_parent_id,
    cat_name,
    0 AS level
  FROM category
  WHERE cat_parent_id = 0
  UNION ALL
  SELECT 
    cat.cat_id, 
    cat.cat_parent_id,
    cat.cat_name,
    (level + 1) AS level
  FROM category cat
  INNER JOIN cte c
    ON c.cat_id = cat.cat_parent_id
)

SELECT 
  cat_id, 
  cat_parent_id,
  cat_name,
  level
FROM cte
--ORDER BY level;
Stack Overflow
  • 2,416
  • 6
  • 23
  • 45