CODEWITHSUNDEEP
mysqlsqlmysql-5.6
zleek
zleek

Reputation: 217

MySQL Select parents and childs in proper order with single query

I have a MySQL table with following data:

ID Name      ParentID
1  Foo       null
2  Bar       null
3  Foo SubA  1
4  Bar SubA  2
5  Foo SubC  1
6  Foo SubB  1

I would like to retreive all data with following order:

1  Foo       null
3  Foo SubA  1
6  Foo SubB  1
5  Foo SubC  1
2  Bar       null
4  Bar SubA  2

Is it possible with MySQL and single query?

Upvotes: 0

Views: 139

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

If this is a two-level hierarchie, i.e. no grandparents and grandchildren, it's a mere ORDER BY clause:

select id, name, parentid
from mytable
order by coalesce(parentid, id), parentid is not null, name;

This makes use of MySQL's true = 1, false = 0. parentid is not null is 0 for the parent and 1 for the children.

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

You could use recursive CTE (MySQL 8.0+):

-- 2 level hierarchy (parent-child)
WITH RECURSIVE cte AS
(
   SELECT tx.*, 1 AS lvl, ID AS grp FROM tx WHERE ParentID IS NULL
   UNION ALL 
   SELECT tx.*, lvl+1, cte.ID FROM tx JOIN cte WHERE tx.ParentId = cte.Id
)
SELECT ID, Name, ParentId
FROM cte
ORDER BY grp, lvl, Name;

DBFiddle Demo

Upvotes: 0

Related Questions