mysql join and count

Question

Somehow am not successful with creating the query that I want.

DB is to do with locations, there are the following tables which are relevant

• t_location - list of locations incl. field t_location_zipcode, and t_location_id_location
• t_zipcodecity - join table just t_zipcodecity_zipcode t_zipcodecity_id_city
• t_city - city list with t_city_id_city
• t_citystate - join table, t_citystate_id_city, t_citystate_id_state
• t_state - list of states with t_state_id_state

Initially I tried to get a list of states with locations using this query:

SELECT DISTINCT `t_state_id_state`
      , `t_state_name_full` 
  FROM (`t_state`) 
LEFT JOIN `t_citystate` ON `t_state_id_state` = `t_citystate_id_state` 
LEFT JOIN `t_city` ON `t_citystate_id_state` = `t_city_id_city` 
LEFT JOIN `t_zipcodecity` ON `t_city_id_city` = `t_zipcodecity_id_city` 
LEFT JOIN `t_location` ON `t_zipcodecity_zipcode` = `t_location_zipcode` 
ORDER BY `t_state_name_full` asc ­

which works fine.

Now what I also need / want which I am failing dismally at is to get the number of locations in each state. I don't know if it can be done in this one query or if i need another, either way I need help!

Answer 1

you can use a count and a group by. Something like this:

SELECT DISTINCT `t_state_id_state`
          , `t_state_name_full`
          , COUNT(*) 
      FROM (`t_state`) 
 LEFT JOIN `t_citystate` ON `t_state_id_state` = `t_citystate_id_state` 
 LEFT JOIN `t_city` ON `t_citystate_id_state` = `t_city_id_city` 
 LEFT JOIN `t_zipcodecity` ON `t_city_id_city` = `t_zipcodecity_id_city` 
 LEFT JOIN `t_location` ON `t_zipcodecity_zipcode` = `t_location_zipcode` 
  GROUP BY `t_state_id_state` , `t_state_name_full`
  ORDER BY `t_state_name_full` asc