3

My table structure:

id | name      | parent_id
0  | "name0"   | NULL
1  | "name1"   | 0
2  | "name2"   | 1
...

I need something like:

id | full_path
2  | "name0|name1|name2"

2 is specified by user; path's length is unknown. How can do produce something like this with MySQL?

Krzysztof Stanisławek
  • 1,267
  • 4
  • 13
  • 27
  • 1
    This is, at best, a very strange task to ask of the database. Perhaps it's a manipulation that you should perform in the presentation layer of your application (i.e. in the program code that accesses MySQL)? – eggyal Mar 04 '15 at 19:00
  • To elaborate on my previous comment, this is the third symptom of [Pinball Programming](http://www.yacoset.com/Home/signs-that-you-re-a-bad-programmer#TOC-3.-Pinball-Programming) as listed in the excellent article "**Signs that you're a bad programmer**". – eggyal Mar 04 '15 at 19:05
  • 1
    You're probably right. I'm just looking for a simple tool to perform specific task using a specific database. I understand your meaning, I'm going to write my own tool then, thank you. – Krzysztof Stanisławek Mar 04 '15 at 20:21
  • 1
    As far as I know, MySQL doesn't support [recursive common table expressions](http://stackoverflow.com/questions/8833535/how-to-transform-a-mssql-cte-query-to-mysql), which is a shame – Vladimir Baranov Mar 04 '15 at 22:45
  • http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/ – bdn02 Mar 04 '15 at 22:50
  • @VladimirBaranov That is not correct (at least not anymore?), see https://www.mysqltutorial.org/mysql-recursive-cte/. MySQL Keyword is 'WITH RECURSIVE'. – jasie Jan 24 '20 at 08:50
  • @jasie, good for MySQL developers to finally add this standard feature. Write your answer to this question and show us how to use it. – Vladimir Baranov Jan 24 '20 at 13:40

1 Answers1

-2

You can use this query :

select id,(select  GROUP_CONCAT(CONCAT(name) SEPARATOR '|') as full_path from test ) 
full_path from test where id=2
HamzaNig
  • 1,019
  • 1
  • 10
  • 33
  • 1
    While this code may answer the question, providing additional [context](https://meta.stackexchange.com/q/114762) regarding _how_ and/or _why_ it solves the problem would improve the answer's long-term value. Remember that you are answering the question for readers in the future, not just the person asking now! Please [edit] your answer to add an explanation, and give an indication of what limitations and assumptions apply. – Dev-iL Jun 19 '18 at 14:09
  • i think dont need to explaine cuz it just a sample query – HamzaNig Jun 21 '18 at 17:57
  • I agree with @Dev-iL - see https://stackoverflow.com/help/how-to-answer. – jasie Jan 24 '20 at 08:47