0

I have a table with multiple herarchy. There is four levels and it is reprsented this way.

ID |   NAME | PARENT 
1       A      NULL     
2       B        1        
3       C        2        
4       D        3

The query should display every signe category with the top level. The result would be this way. 

   ID  | TOP LEVEL 
    2        1        
    3        1        
    4        1

For now, I did a query that show me every category and the direct parent of it.

SELECT CB.name as 'child-category',CA.name as 'parent- 
category'
FROM category CA
INNER JOIN category CB 
ON CA.id = CB.parent
where CA.id <> CB.category
ORDER BY CA.id
;

How can I modify the query to get the top parent of all the categories.

Lithilion
  • 1,097
  • 2
  • 11
  • 26
KubiRoazhon
  • 1,759
  • 3
  • 22
  • 48
  • Possible duplicate: https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query – Tim Biegeleisen Oct 04 '18 at 09:32
  • Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) – Madhur Bhaiya Oct 04 '18 at 09:59
  • Maybe you're searching something like "Nested set model", more info here: https://github.com/cn007b/my/blob/master/ed/mysql/nestedSetmodel.md – cn007b Oct 04 '18 at 10:23

0 Answers0