0

Is it possible to generate (join) a new field by combining existing fields as source ?

I have a table like this:

id | title | parent_id | path
1  | a     | 0         | 1
2  | b     | 1         | 1/2
3  | c     | 1         | 1/3
4  | d     | 3         | 1/3/4

I want to use the "path" field's string (numbers) as a kind of index and generate (join) a new field using titles insead ids like:

id | title | parent_id | path | title_path
1  | a     | 0         | 1    | a
2  | b     | 1         | 1/2  | a/b
3  | c     | 1         | 1/3  | a/c
4  | d     | 3         | 1/3/4| a/c/d

Is this possible using sql on itself or should I use php for this?

Skeletor
  • 3,201
  • 4
  • 30
  • 51
  • 1
    see this https://stackoverflow.com/questions/18106947/cte-recursion-to-get-tree-hierarchy/18111876 or https://stackoverflow.com/questions/15585155/sql-server-query-for-tree-path-of-one-item – fmalh3iro Nov 27 '19 at 15:17
  • 1
    Now you see why delimited lists of pretty much anything is a bad idea in a database. Proper database design would have made this quite easy – RiggsFolly Nov 27 '19 at 15:25
  • 1
    @Skeletor . . . It is probably easier to use PHP. In MySQL 8+, you can use a recursive CTE if need be. – Gordon Linoff Nov 27 '19 at 16:12
  • @GordonLinoff you are right. Will modify it with php, looks best way for me. – Skeletor Nov 27 '19 at 16:13

0 Answers0