Mysql Complex加入查询问题

Question

I have 5 mysql tables as described below. clinics table

id
name

d_location_subscription table

id
clinic_id
t_id   //t_id will contain a foreign key of d_cities, d_states or d_countries table
type   "country" "state" "city"

d_countries table

id
name
code

d_states table

id
d_country_id
name
code

d_city table

id
d_state_id
name
code

d_location_subscription table is used to record clinic's subscription for a location(it may be a city, state or country). I'm expecting to get all subscribed cities for a specific clinic using d_location_subscription table.

For example, if clinic A is subscribed to Texas state, I should be able to get all city ids for clinic A.

I created following sql query, it looks ugly but generate a close result what i want to achieve.

select 
    `d`.`id` AS `clinic_id`,
    if((`dct`.`id` is not null),`dct`.`id`,if((`dct1`.`id` is not null),`dct1`.`id`,`dct2`.`id`)) AS `d_city_id` 
from ((((((((
    `d_location_subscriptions` `dls` 
    join `clinics` `d` 
        on((`d`.`id` = `dls`.`clinic_id`))) 
    left join `d_countries` `dc` 
        on(((`dc`.`id` = `dls`.`t_id`) and (`dls`.`type` = 'country')))) 
    left join `d_states` `ds` 
        on((`ds`.`d_country_id` = `dc`.`id`))) 
    left join `d_cities` `dct2` 
        on((`dct2`.`d_state_id` = `ds`.`id`))) 
    left join `d_states` `ds1` 
        on(((`ds1`.`id` = `dls`.`t_id`) and (`dls`.`type` = 'state')))) 
    left join `d_cities` `dct` 
        on((`dct`.`d_state_id` = `ds1`.`id`))) 
    left join `d_cities` `dct1` 
        on(((`dct1`.`id` = `dls`.`t_id`) and (`dls`.`type` = 'city')))) 
) 

when there is record with type "country" in d_location_subscription table, I receive following result. total number of records returned are equal to the number of d_states table records.

How should I get rid of those Null values by changing above query? And please advice me if this is the correct way to acheive similar functionality. Thanks in advance :)

Answer 1

the IF() computed column is in essence what STT LCU was trying to offer, but you can't use that directly in the where for some reason.

I've rewritten your query, but with different aliases to better follow the origination of the tables / relationships to get the data. In the end, I've added a where to test for ANY ONE of the "ID" values as NOT NULL. If they are ALL Null, the record should be excluded..

select 
      d.id AS clinic_id,
      if(CityViaState.id is not null, CityViaState.id,
         if( ByCity.id is not null, ByCity.id, CityViaCountry.id )) AS d_city_id 
   from 
      d_location_subscriptions dls 
         join clinics d 
            ON dls.clinic_id = d.id 

         left join d_countries ByCountry 
            ON dls.t_id = ByCountry.id 
            and dls.type = 'country'
            left join d_states StateViaCountry 
               ON ByCountry.id = StateViaCountry.d_country_id 
               left join d_cities CityViaCountry 
                  ON StateViaCountry.id = CityViaCountry.d_state_id 

         left join d_states ByState 
            ON dls.t_id = ByState.id 
            and dls.type = 'state'
            left join d_cities CityViaState 
               ON ByState.id = CityViaState.d_state_id

         left join d_cities ByCity 
            ON dls.t_id = ByCity.id 
            and dls.type = 'city'
   where
         CityViaState.id is not null
      OR ByCity.id is not null
      OR CityViaCountry.id is not null