Mysql: Count in column (with join)

Question

I have the following query:

Table zone: zone_id, country_id, city_id

SELECT zone.zone_id, city_name
FROM zone
INNER JOIN city
ON city.city_id = zone.city_id
WHERE country_id = 3

result:

zone_id | city_id
----------------------
17      | New York
16      | Los Angeles

And Other query:

Table zone , Table management relation: zone_id

SELECT COUNT(user_id)
FROM management
WHERE zone_id = 17

SELECT COUNT(user_id)
FROM management
WHERE zone_id = 16

result

first query: Count             second query:  Count
             -------                          --------
             5                                2

How I can join the count () to each row of the result of the first query?, in this way:

zone_id | city_id      | users
--------------------------------
17      | New York     | 5
16      | Los Angeles  | 2

I tried this:

SELECT zone.zone_id, vNombre, COUNT(user_id) AS users
FROM zone
INNER JOIN city
ON city.city_id = zone.city_id
INNER JOIN management
ON management.zone_id = zone.zone_id
WHERE country_id = 3
GROUP BY user_id

but I find this:

zone_id | city_id      | users
--------------------------------
17      | New York     | 5
17      | New York     | 2

Answer 1

SELECT   zone_id, city_name, COUNT(user_id) AS users
FROM     zone
  INNER JOIN city       USING (city_id)
   LEFT JOIN management USING (zone_id)
WHERE    country_id = 3
GROUP BY zone_id, city_name