Countdown in two tables

Question

I’m having trouble counting items pertaining to neighborhoods. I need to list all the neighborhoods of the city X and show the items available cars and motorcycles. In the example I only used one city to facilitate the illustration. I used group by and left join but did not count correctly.

I don’t know if the error is mine, or if there is a difficulty in using Join in this case, but some solutions I found suggest something like the query below:

_{Ref 1}

SELECT
  (SELECT COUNT(*) FROM table1 WHERE someCondition) as table1Count, 
  (SELECT COUNT(*) FROM table2 WHERE someCondition) as table2Count,
  (SELECT COUNT(*) FROM table3 WHERE someCondition) as table3Count

_{Ref 2}

SELECT co.*, 
    (SELECT COUNT(*) FROM modules mod WHERE mod.course_id=co.id) AS moduleCount, 
    (SELECT COUNT(*) FROM videos vid WHERE vid.course_id=co.id) AS vidCount
FROM courses AS co
ORDER BY co.id DESC

The above references make the condition very repetitive. Some join meet this need?

---

_table

TBL.BAIRROS
ID | CIDADE | BAIRRO
1  | 1      | Meier
2  | 1      | Copacabana

TBL.CARROS
ID | BAIRRO | MODELO
1  | 1      | Modelo 1
2  | 1      | Modelo 2

TBL.MOTOS
ID | BAIRRO | MODELO
1  | 2      | Modelo 1

_{expected output}

array(
    0 => array
         bairro => Meier
         carros => 2
         motos  => 0

    1 => array
         bairro => Copacabana
         carros => 0
         motos  => 1
)

_{using left Join}

...
, count( carros.id ) carros
, count( motos.id  ) motos
from bairros
left join motos  on motos.bairro  = bairros.id
left join carros on carros.bairro = bairros.id
group by bairros.bairro

---

_Update

While writing I found a possible solution using count( DISTINCT ... ). The output came out as expected, but wanted to know if the solution only circumvents a query problem or if it is the correct way.

...
, count( DISTINCT carros.id ) carros
, count( DISTINCT motos.id  ) motos
from bairros
left join motos  on motos.bairro  = bairros.id
left join carros on carros.bairro = bairros.id
group by bairros.bairro

Answer 1

Try JOIN with COUNT defining the field that will be counted because it ignores the null records.

SELECT
  bairro,
  COUNT(idCarro) AS qtde_carros,
  COUNT(idMoto) AS qtde_motos
FROM bairro b
  LEFT JOIN carros c ON (c.idBairro = b.idBairro)
  LEFT JOIN motos m ON (m.idBairro = b.idBairro)
GROUP BY bairro