CODEWITHSUNDEEP
mysqlsqldatabase
user2137179
user2137179

Reputation:

How can I select data and get a hierarchy result

I have a table that contains categories list

Table Structure

- ID
- category_name
- parent_category_ID // 0 indicates no parent

Assuming the following data is in the table

ID  | category_name | parent_category_ID
1   | verbs         | 0
2   | adjectives    | 0
3   | nouns         | 0
4   | go            | 1
5   | color         | 3
6   | bag           | 3
7   | good          | 2
8   | play          | 1
9   | polite        | 2
10  | t1            | 5
11  | t2            | 5
12  | t3            | 8

I want the result to be in this order

ID  | category_name | parent_category_ID
1   | verbs         | 0
4   | go            | 1
8   | play          | 1
12  | t3            | 8
2   | adjectives    | 0
7   | good          | 2
9   | polite        | 2
3   | nouns         | 0
5   | color         | 3
10  | t1            | 5
11  | t2            | 5
6   | bag           | 3

which will make the result appear as they are like

verbs
--- go
--- play
--- --- t3
adjectives
--- good
--- polite
nouns
--- color
--- --- t1
--- --- t2
--- bag

I actually got no idea how to start doing this as my SQL experience is still beginner.

Upvotes: 1

Views: 51

Answers (1)

Milen
Milen

Reputation: 8867

Not quite sure if I understood this correctly but try this:

SELECT 
      id, 
      category_name, 
      parent_category_ID
FROM  yourTableName
ORDER BY CASE 
WHEN parent_category_ID = 0 
THEN ID 
ELSE parent_category_ID END, id

Demo: SQL FIDDLE

Upvotes: 1

Related Questions