-1

I have a mysql database table which has list of locations with there parent location id in hierarchical manner so i want to list all the locations with their root parent location

Following is the sample table

id     location_name  parent_id
1         a             0            
2         b             0
3         c             0
4         d             1
5         e             1
6         f             5
7         g             4
8         k             2
9         l             8
10        j             9

Following is the result i want

id     location_name  root_parent_name
1         a             a            
2         b             b
3         c             c
4         d             a
5         e             a
6         f             a
7         g             a
8         k             b
9         l             b
10        j             b

parent name is root parent location

Bharat Vishe
  • 153
  • 1
  • 2
  • 12
  • You request is unclear! What is the relation between the `parent_name` and the `parent_id`? – Hamza Abdaoui Feb 23 '18 at 11:11
  • And what have you tried so far? Where did you get stuck? – Shadow Feb 23 '18 at 11:22
  • @HamzaAbdaoui actually its root_parent_name. I have edited the question – Bharat Vishe Feb 23 '18 at 11:31
  • @Shadow i have refer this [link](https://stackoverflow.com/a/18894775) answer and made following query but no luck with the same `query`select @location_id:=l.parent_id, l.location_name, ( select location_name from (select @parent:=parent_id, location_name from (select @parent:=@location_id) a join (select * from location_master where location_type_id != 1 order by id desc) b where @parent=id order by @parent asc limit 1) c ) as root_parent from location_master l`query` – Bharat Vishe Feb 23 '18 at 11:38
  • you cannot do that in single query in mysql, it lacks recursive queries, as your parent child tree can be of any size – vicky Feb 23 '18 at 12:02

1 Answers1

0

Try the following 

 SELECT t1.name as parent, t2.`name` as name FROM `test` t1 RIGHT JOIN `test` t2 ON t2.`p_id` = t1.id
Tijo John
  • 686
  • 1
  • 9
  • 20