I have the documenttype table as a nested /hierarchical/inherited table (I don't know the correct name) that has the following fields:
-id
-nombre
-padre_id
-...
the data I have is:
1 | local | 0
2 | contable | 1
3 | caja_2019 | 2
4 | caja_2020 | 2
5 | certificados | 0
what I want is to go through each row and concatenate it with the name of the parent so that they return a path where I will insert files. I have this query:
WITH RECURSIVE niveles AS (
SELECT
nombre
FROM tipodocumento
WHERE padre_id = 0
UNION ALL
SELECT
CONCAT(td.tNombre, '/', td2.tNombre )
FROM tipodocumento td
INNER JOIN tipodocumento td2 ON td.id= td2.padre_id
)
SELECT *
FROM niveles
and the values returned to me are
- local
- local/accounting
- accounting/cash_2019
- accounting/cash_2020
- local/certificates
and while some are fine, others don't go all the way to the first parent. how could i solve that?
I found the solution, I leave it in case it helps someone:
I think your problem is that the element with id 0 does not exist, you must make sure that all the IDs exist and it would be good if you defined how many levels they are