use sql to print parent_id and sub id

Asked Apr 21 '17 at 10:33

Active Apr 21 '17 at 11:10

Viewed 153 times

Lets say I have a table names like this

|   id  |   parent_id   |   title          |
|    1  |        0      |   Boys           |
|    2  |        0      |   Girls          |
|    3  |        1      |   John           |
|    4  |        2      |   Mary           |
|    5  |        1      |   Felix          |
|    6  |        5      |   Felix-Malcom   |
|    7  |        4      |   Mary-Mali      |
|    8  |        5      |   Felix-Edgar    |
|    9  |        4      |   Mary-Ally      |
|   10  |        3      |   John-Mack      |

What is the best method of printing all this data to look like this:

Boys

John

John-Mack

Felix

Felix-Malcom

Felix-Adgar

Girls

Mary

Mary-Mali

Mary-Ally

How can I print the data like this? Should I use a inner join or do I have to use a loop inside another loop? What can you recommend?

EDIT: I dont know if this changes anything, but I use mariadb

edited Apr 21 '17 at 11:02

asked Apr 21 '17 at 10:33

William82

How do you know if a person is a boy or a girl? – jarlh Apr 21 '17 at 10:35
@jarlh Parent ID. – Praveen Kumar Purushothaman Apr 21 '17 at 10:35
I guess it should be looping, but people hate that technique quoting performance constraints. – Praveen Kumar Purushothaman Apr 21 '17 at 10:36
Do you alway have those three levels (boy/girl, name, composite name) or can the levels number be arbitrary? – Stefano Zanini Apr 21 '17 at 10:37
1

@PraveenKumar, very odd data model... Felix-Malcom is a boy since the parent Felix is a boy? – jarlh Apr 21 '17 at 10:39
It could have been an interesting discussion around names, but the problem are not the names, but how I cant print a series of data when I know the start (parent_id=0) – William82 Apr 21 '17 at 10:41
You probably want some sort of recursive query, which mysql doesn't natively support. Search this site for "mysql recursive query" and you'll get some ideas. – Jerrad Apr 21 '17 at 10:42

use sql to print parent_id and sub id

0 Answers0