SELECT the table in hierarchy manner

Question

For example, I have a table like this:

----------------------
| id | Name | Parent |
......................
| 1  | Joe  |  ''    |
| 2  | Alice|  ''    |
| 3  | Manny|  ''    |
| 4  | kid1 |  1     |
| 5  | kid2 |  1     |
| 6  | kid3 |  3     |

and I want to display it in a hierarchy manner like this:

| id | Name | Parent |
......................
| 1  | Joe  |  ''    |
| 4  | kid1 |  1     |
| 5  | kid2 |  1     |
| 2  | Alice|  ''    |
| 3  | Manny|  ''    |
| 6  | kid3 |  3     |

Can I do it using only SQL commands?

Thank you so much guys. I'm new to stackoverflow, yet I'm already amazed by how fast you have answered my question.

@amar duplantier, Thanks for the link it solved my problem!! I can't find that thread when I searched before. I'm sorry I didn't provide enough information for my question.

here's the code I use based on amar's link:

select  *
from Table a
order by
        case 
        when Parent = '' 
        then id 
        else    (
                select  id 
                from    Table parent 
                where   parent.id = a.Parent
                ) 
        end
DESC

Any particular RDBMS? MySQL? SQL Server? Also, is the structure always only one level (ie no grand parents)? — Joachim Isaksson, May 26 '14 at 06:19
Check out this sql fiddle http://sqlfiddle.com/#!3/80369/6. Also check this question http://stackoverflow.com/questions/8022158/how-do-i-order-by-parent-then-child — kelsier, May 26 '14 at 06:38

score 0 · Answer 1 · answered May 26 '14 at 06:36

You didn't mention a database in particular, so allow me to provide a solution using Oracle.

In Oracle you can perform START WITH and CONNECTED BY:

select id, Name, Parent from your_table start with parent is null connect by prior id = parent;

Not all database have connected by, such as MySQL, but you can simulate it using functions: http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/

score 0 · Answer 2 · answered May 26 '14 at 06:44

Try Recurcive CTE:

WITH HierarchyTree (id, Name, Parent)
AS
(
-- Anchor member definition
    SELECT e.id, e.Name, e.Parent
    FROM MyTable AS e
    WHERE id = 1
    UNION ALL
-- Recursive member definition
    SELECT e.id, e.Name, e.Parent
    FROM MyTable AS e
    INNER JOIN HierarchyTree AS d
        ON e.Parent = d.id
)
SELECT id, Name, Parent
FROM HierarchyTree
GO

score 0 · Accepted Answer · answered May 26 '14 at 14:41

0

If the '' in the Parent column mean empty string then

SELECT   *
FROM     table1
ORDER BY Concat(Parent, ID)
       , ID

will return a resultset with the order the OP want (if there is only only level of hierarchy)

answered May 26 '14 at 14:41

Serpiton

3,676
3
24
35

wow, never thought of this method before. Yes this is working very good and short/not complex as well. Thanks! – Konthorn May 27 '14 at 08:09
@Konthorn it uses the fact that the string order for number is not the same of the number order, it's probably the first time that I used it on purpose and not only to demostrate the difference between the two – Serpiton May 27 '14 at 08:31

SELECT the table in hierarchy manner

3 Answers3