multi level cascade inner join select query

Question

I've following table structure

I'd like to select post_id from any available data of short_name(country name), name (state table) or region_name. Executing following query true result for region_name but not for 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%

Tell me please, where I'm mistaking!

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%'

Answer 2

Use a distinct table expression for each of your three criteria. Write your OR logic using UNION in SQL:

select post_id
from t_post_city
inner join t_region on t_region.region_id = t_post_city.city_id
where t_country.short_name like %india%
t_region.region_name like %sitapura%
union
select post_id
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
where t_country.short_name like %india%
union
select post_id
from t_post_city
inner join t_region on t_region.region_id = t_post_city.city_id
inner join t_states on t_region.province_id = t_states.state_id
where t_states.name like %rajasthan%;