How can I handle database from Hierarchical data in Mysql?

Question

I have table Location like this:

ID,   PID,   Location
1    NuLL    Country
2    1       City
3    2       County
4    3       District
5    4       Social
...  ...     ....

How can I handle my database in MySQL that returns this:

ID,   Location,   Full Location
1     Country     Country
2     City        City-Country
3     County      County-City-Country
4     District    District-County-City-Country
5     Social      Social-District-County-City-Country
...   ...         ...

Does your data only consist of a single chain, as suggested by the data in the question? Or is it more complex? — Gordon Linoff
– Gordon Linoff, Commented Dec 4, 2014 at 3:43
Until now, my data consist a single chain. I'm very terrible about database, what do you mean about "more complex"? Simple example? — gianglaodai
– gianglaodai, Commented Dec 4, 2014 at 3:51
I think what is meant, do you have multiple parents or is Country the only parent (where pid = null)? If you have multiple parents, are your expected results to show all parents and children, or just a specific parent/child? — sgeddes
– sgeddes, Commented Dec 4, 2014 at 4:32

sgeddes · Accepted Answer · 2014-12-04 04:40:55Z

2

If you only have a single parent, then you can use this method which acts as a recursive query:

select id, location, @pid:=id as 'pid' ,
  @newlocation:=if(pid is null, 
                   location, 
                   concat_ws(',',@newlocation,location)) newlocation
from (select * from Location order by pid) t
  join (select @pid:=null, @newlocation:='') p 
     on coalesce(t.pid,0)=coalesce(@pid,0)

SQL Fiddle Demo

However, if you have multiple parents, this will only return one of those with its children. It can be modified to work with a single parent/child combination, but not with multiple parents.

answered Dec 4, 2014 at 4:40

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

harvey · Accepted Answer · 2014-12-04 05:03:00Z

Dealing with hierarchies is a tricky situation in the SQL world, in many cases programming languages offer better options to deal with this type of data - such as dictionaries and linked lists.

However, you can still achieve what you want by aliasing the table;

SELECT t1.ID, t1.Location, 
       CONCAT( 
          IF(ISNULL(t1.Location) , '', t1.Location)
        , IF(ISNULL(t2.Location) , '', CONCAT('-',t2.Location))
        , IF(ISNULL(t3.Location) , '', CONCAT('-',t3.Location))
        , IF(ISNULL(t4.Location) , '', CONCAT('-',t4.Location))
       ) as Full_Location
FROM Locations as t1
      left join Locations as t2 on t1.PID = t2.ID
      left join Locations as t3 on t2.PID = t3.ID
      left join Locations as t4 on t3.PID = t4.ID

This example assumes you only want to go four deep. It's possible to go deeper of course, just keep following the same structure.

There is a full write up on these hierarchical data and associated queries at http://ftp.nchu.edu.tw/MySQL/tech-resources/articles/hierarchical-data.html

Collectives™ on Stack Overflow

How can I handle database from Hierarchical data in Mysql?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related