How to mount this SQL

Question

I have these tables:

Client:

cliente
------- 
id nome 
id_municipio

Municipality:

municipio
-------
id
nome

Regional:

regional
-------
id
nome

Regional_municipio:

regional_municipio
-------
id_municipio
id_regional

On the table regional_municipio i put for example, municipio 17 refers to regional 1

I need to make a select give a count client by regional.

For example:

• Regional 1 has 500 customers
• Regional 2 has 50 customers
• Regional 3 has 100 customers

Being that in the client table I only have the county number, how to do this?

Answer 1

Thus:

SELECT COUNT(*) AS QTDE, C.NOME FROM CLIENTE A LEFT JOIN  REGIONAL_MUNICIPIO B 
LEFT JOIN REGIONAL C ON C.ID = B.ID_REGIONAL
ON A.ID_MUNICIPIO = B.ID_MUNICIPIO
GROUP BY C.NOME

Answer 2

You have the id_municipio to identify the client’s municipality, just make a link to the table regional_municipio and make Count! I believe this SQL solves your problem.

SELECT COUNT(c.id) FROM cliente c
INNER JOIN regional_municipio rm
ON rm.id_municipio = c.id_municipio
WHERE rm.id_regional = /*ID_REGIONAL*/