MySQL - рекурсия с SQL-запросом родительско-дочерних отношений

Не меняя таблицы? Соедините местоположения с собой во всех полях, которые определяют отношение.

Пример:

select loc.name, job.*
from Locations as loc
join Locations as parent_loc 
  on parent_loc.name in (loc.name, loc.continent, loc.country, loc.admin1, loc.admin2, loc.city)
join Jobs as job on job.location_id = loc.id
where parent_loc.name = 'UK'

Но эта модель на самом деле не нормализована. Слишком много дублированных имен.
Возможно, стоит использовать внешние ключи к ПК таблицы.
Тогда смена имени не станет маленькой проблемой.

Например, как в этом тесте

Или вы можете переключиться на модель вложенного набора.
Это не потребовало бы рекурсии, как Модель Списка Смежности.
Больше информации об этих 2 моделях здесь

Используемая вами модель базы данных страдает от двух основных проблем:

• Имеет правила нерегулярной иерархии. Первая строка следует другим правилам, чем остальная часть таблицы.
• Иерархия жестко запрограммирована, имея не более 6 уровней. Что произойдет, если некоторым сообществам потребуется больше уровней?

В любом случае, можно получить имеющуюся у вас информацию, хотя и с использованием некрасивого запроса SQL. Вот оно:

select
  j.*  
from (
  select -- select the initial location
  from locations l
  where name = 'Yorkshire'
  union
  select -- select all children locations
  from locations l
  join locations r 
    on (l.continent is null and l.name = r.continent)
    or (l.continent is not null and l.country is null and l.name = r.country)
    or (l.continent is not null and l.country is not null and l.admin1 is null and l.name = r.admin1)
    or (l.continent is not null and l.country is not null and l.admin1 is not null and l.admin2 is null and l.name = r.admin2)
    or (l.continent is not null and l.country is not null and l.admin1 is not null and l.admin2 is not null and l.city is null and l.name = r.city)
  where l.name = 'Yorkshire'
  ) x    
join jobs j on j.location_id = x.id

Примечание : этот запрос не использует CTE (Common Table Expressions), поэтому он подходит для MySQL 5.x, а также MySQL 8.x. ]

Вы можете использовать выражение case when:

select  jobs.*  
from    (
        select     id 
        from       locations 
        where      name = "Europe"
        union all
        select     child.id 
        from       locations main
        inner join locations child 
                on main.name = case when main.continent is null then child.continent
                                    when main.country   is null then child.country
                                    when main.admin1    is null then child.admin1
                                    when main.admin2    is null then child.admin2
                                                                else child.city
                               end
        where      main.name = "Europe"
        ) sub
inner join jobs 
        on jobs.location_id = sub.id