Mysql Complex Join Query Issue

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.

this is the result

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 :)

Shouldn't you just be able to use INNER JOIN instead of LEFT JOIN to achive this. — Cyclonecode
– Cyclonecode, Commented Sep 16, 2013 at 15:35
@Krister Andersson If I use inner join no results are returned at all — EastSw
– EastSw, Commented Sep 16, 2013 at 15:38
@priyan99 - I don't think you can use INNER JOIN throughout the whole query, only in certain places where it's appropriate. Likely is that you could use a INNER JOIN where you're joining the cities ie INNER JOIN d_cities` dct2 ` and so on. — Cyclonecode
– Cyclonecode, Commented Sep 16, 2013 at 15:39

STT LCU · Accepted Answer · 2013-09-16 15:37:18Z

1

The quickest, dirtiest way to achieve what you want is just to append this where condition to your query:

WHERE d_city_id is not null

but you might prefer to rework your query and decide where you really need LEFT joins and not INNER joins

answered Sep 16, 2013 at 15:37

STT LCU

4,3304 gold badges32 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

EastSw Over a year ago

I receive "#1054 - Unknown column 'd_city_id' in 'where clause'" if I do that.

DRapp · Accepted Answer · 2013-09-16 16:01:58Z

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

Collectives™ on Stack Overflow

Mysql Complex Join Query Issue

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related