sql - 多级级联内连接选择查询

Question

我有以下表格结构

我想从 short_name(国家名称)、名称(状态表)或 region_name 的任何可用数据中选择 post_id。执行以下查询结果为 region_name 但不是 short_name(country name), name (state table)。

select *
from t_post_city
inner join t_region on t_region.region_id = t_post_city.city_id
inner join t_country on t_region.country_id = t_country.country_id
inner join t_states on t_region.province_id = t_states.state_id
where t_country.short_name like %india%
   or t_states.name like %rajasthan%
   or t_region.region_name like %sitapura%

请告诉我，我错在哪里!

Answer 1

select *
from t_post_city
LEFT OUTER join t_region on t_region.region_id = t_post_city.city_id
LEFT OUTER join t_country on t_region.country_id = t_country.country_id
LEFT OUTER join t_states on t_region.province_id = t_states.state_id
 where t_country.short_name like '%india%'
 or t_states.name like '%rajasthan%'
 or t_region.region_name like '%sitapura%'