CODEWITHSUNDEEP
mysql
John Smith
John Smith

Reputation: 1814

mysql join and count

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

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!

Upvotes: 3

Views: 120

Answers (3)

John Smith
John Smith

Reputation: 1814

Ok thats looking good, my bad with the left joins, i was initially trying to get ALL the states which is why i used them. But i change that to use inner joins

SELECT  t_state_id_state,
t_state_name_full,
COUNT(DISTINCT t_location_id_location) AS locations_number
FROM t_state
INNER JOIN t_citystate ON t_citystate_id_state = t_state_id_state 
INNER JOIN t_city ON t_city_id_city = t_citystate_id_city 
INNER JOIN t_zipcodecity ON t_zipcodecity_id_city = t_city_id_city 
INNER JOIN t_location ON t_location_zipcode = t_zipcodecity_zipcode 
GROUP BY t_state_id_state 
ORDER BY t_state_name_full ASC

then i actually end up with a result that looks good !

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

SELECT  t_state_id_state
      , t_state_name_full 
      , COUNT(DISTINCT t_location_id_location) AS locations_number

  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 

  ORDER BY t_state_name_full ASC ­

Upvotes: 0

northpole
northpole

Reputation: 10346

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

Upvotes: 1

Related Questions