1

I have mysql table 

    region_id | name | parent_id
    1 | Österreich   | 0
    3 | Deutschland  | 0
    4 | Bayern       | 3
    5 | Bad Griesbach| 4
    6 | Nordrhein-Westfalen| 3
    7  | Île-de-France | 7
    ....

I write the query

        SELECT 

                h.hotel_id,
                h.hotel_name,
                t1.region_id,
                t1.name AS name1,
                t2.region_id,
                t2.name AS name2,
                t3.region_id,
                t3.name AS name3

            FROM
                hotel_service.hotel h
                LEFT JOIN region_service.region t1 ON t1.region_id = h.region_id
                LEFT JOIN region_service.region t2 ON t2.region_id = t1.parent_id
                LEFT JOIN region_service.region t3 ON t3.region_id = t2.parent_id

            WHERE
                hotel_id IN ('10640' , '10003', '10004', '10005', '10007')

and result like this

hotel_id hotel_name city_id name1   region_id   name2 region_id name3   
    10003   Dorfhotel    231    Sylt    30   Schleswig    3   Deutschland               
    10004   Iberotel    22  Boltenhagen 21  Mecklenburg   3   Deutschland               
    10005    Hotel  170    Barcelona   169  Katalonien   168    Spanien             
    10007   Schlosshotel 175 Rügen  21  Mecklenburg       3   Deutschland               
    10640   Hotel Berlin  36    Berlin  3   Deutschland

but the problem in order of result for example hotel_id 10640 - 3 Deutschland shows in wrong column, its should show on last column because last column is for country.

user6186914
  • 31
  • 5
  • Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) – Raymond Nijland Feb 28 '18 at 16:11
  • 1- the query don't match the result you have shown as in the result you have city_id not present into the query 2- this behaviour can have 2 causes or wrong parent mapping or t2.parent_id is null and you have a "smaller" row – TheOni Feb 28 '18 at 16:12
  • @RaymondNijland Thank you for reply, I already saw that post. but i have problem with some hotel result orders. country should come on name3 column... – user6186914 Feb 28 '18 at 16:30
  • @TheOni Thank you for reply, I don't think show i have mapping problem because its perfectly work with almost all hotels but have a problem with few hotels.. – user6186914 Feb 28 '18 at 16:36
  • What is parent_id of region_id = 36 in your table? – Serif Emek Mar 01 '18 at 10:11
  • @SerifEmek parent_id of region_id = 36 is 3 – user6186914 Mar 01 '18 at 10:15
  • Then there is no problem. – Serif Emek Mar 01 '18 at 10:16
  • @SerifEmek I have problem with order arrangement, for this hotel country come into second column instead of third column.. column 2 should be empty for this hotel – user6186914 Mar 01 '18 at 10:22

1 Answers1

0

If that is the only problematic case you may try this;

SELECT 

                h.hotel_id,
                h.hotel_name,
                t1.region_id,
                t1.name AS name1,
case when t3.region_id is null then null else t2.region_id end,   
case when t3.region_id is null then null else t2.name end,      
case when t3.region_id is null then t2.region_id else t3.region_id end,   
case when t3.region_id is null then t2.name else t3.name end
            FROM
                hotel_service.hotel h
                LEFT JOIN region_service.region t1 ON t1.region_id = h.region_id
                LEFT JOIN region_service.region t2 ON t2.region_id = t1.parent_id
                LEFT JOIN region_service.region t3 ON t3.region_id = t2.parent_id

            WHERE
                hotel_id IN ('10640' , '10003', '10004', '10005', '10007')
Serif Emek
  • 674
  • 5
  • 13