1

I don't quite get how recursive queries work and how to solve this problem. We were given the table on the left and the structure looks like the tree on the right:

ID | Parent                        1
1     null                        / \
2     1                          2   3
3     1                               \
4     3                                4
5     4                               / \
6     4                              5   6   
7     6                                   \ 
                                           7

I know how to get all the parent nodes of every node... but I don't get how you find the max depth of the tree. Meaning I have to find out how many levels this tree has. We aren't given any more information

I would be super grateful if you could give me a solution for mysql, but any sql statement will help me figuring this out

Thanks in advance!

Puddingloeffel
  • 163
  • 2
  • 9
  • 1
    Which version of mysql ? The newest version supports recursive CTE queries, but earlies version don't have this feature. Dependig on your version the query will be different. – krokodilko Sep 08 '18 at 09:47
  • 1
    If your info are stored in a db, for example in the column parent, may be you can use COUNT to get how many records are in that column and the result is the depth. ps, use count distinct to get only the depth not all parents, in your example using count distinct you dont consider the value 2 and 5 but just 1,3,4,6,7 so the result must be = 5... if i have understand your question ;) – Sigma Sep 08 '18 at 09:49
  • 1
    If you arent on MySQL 8 the recursive CTE from Lucasz below can't work, so you can use the procedure in [this answer](https://stackoverflow.com/questions/5291054/generating-depth-based-tree-from-hierarchical-data-in-mysql-no-ctes) – Thomas G Sep 08 '18 at 09:56
  • Thank you guys so much for your comments, I forgot to mention that I have mysql 8.0 and I can work with recursive. The answer from @Lukasz Szozda works perfectly! – Puddingloeffel Sep 08 '18 at 10:51

1 Answers1

10

You could use RECURSIVE cte (MySQL 8.0):

WITH RECURSIVE cte AS (
   SELECT 1 AS lvl, Parent, id
   FROM tab
   WHERE Parent IS NULL
   UNION ALL
   SELECT lvl + 1, tab.Parent, tab.id
   FROM tab
   JOIN cte
     ON tab.Parent = cte.Id
)
SELECT *  -- MAX(lvl) AS max_depth_of_tree
FROM cte;

Output:

┌──────┬─────────┬────┐
│ lvl  │ Parent  │ id │
├──────┼─────────┼────┤
│   1  │         │  1 │
│   2  │      1  │  2 │
│   2  │      1  │  3 │
│   3  │      3  │  4 │
│   4  │      4  │  5 │
│   4  │      4  │  6 │
│   5  │      6  │  7 │
└──────┴─────────┴────┘

DBFiddle Demo

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275