CODEWITHSUNDEEP
mysqlsql
gianglaodai
gianglaodai

Reputation: 321

How can I handle database from Hierarchical data in Mysql?

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
...   ...         ...

Upvotes: 2

Views: 82

Answers (2)

harvey
harvey

Reputation: 2953

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

Upvotes: 2

sgeddes
sgeddes

Reputation: 62861

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)

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.

Upvotes: 2

Related Questions