Mysql Join Tables And Count Instances

Lets say I have the following tables: Countries --------------------------- | ID | Country Name | --------------------------- | 1 | Greece | | 2 | Italy

Solution 1:

Try this out:

SELECT COUNT(cs.City) as Cities, cn.name as Country
FROM countries cn 
INNER JOIN country_city cs ON cs.Cntr = cn.id 
GROUPBY cn.name

OUTPUT:

2   | Greece
1   | Italy

Solution 2:

There is only one JOIN is needed

SELECT `c`.`name`, COUNT(`c`.id) 
FROM `countries_cities` AS `cc`
JOIN `countries` AS `c`
ON `c`.id = `cc`.country_id
GROUP BY `cc`.country_id

Solution 3:

SELECT cn.Name, COUNT(*)
FROM CountriesAndCities cc
JOIN Countries cn ON (cn.ID = cc.Cntr)
GROUPBY cn.Name

Solution 4:

You only need to group the countries in the countries & cities table:

SELECTCOUNT(1), c.Name 
FROM [countriesAndCities] cnc 
INNERJOIN [country] c ON cnc.cnt = c.id
GROUPBY c.Name