How to mount this SQL

Navigation
3

I have these tables:

Client: 

cliente
------- 
id nome 
id_municipio

Municipality: 

municipio
-------
id
nome

Regional: 

regional
-------
id
nome

Regional_municipality: 

regional_municipio
-------
id_municipio
id_regional

In the regional_municipio table I put, for example, municipality 17 refers to the regional 1

I need to make a select that gives a count of client per regional.

For example:

  • regional 1 has 500 clients
  • regional 2 has 50 clients
  • regional 3 has 100 clients

Since in the client table I only have the number of the municipality, how to do that?

    
asked by anonymous 12.09.2016 / 16:32

2 answers

3

So: 

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
    
12.09.2016 / 16:44
2

You have id_municipio to identify the client's municipality, just make a connection with the regional_municipio table and count! I think this SQL will solve 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*/
    
12.09.2016 / 16:39
Could you add a UNIQUE field that accepts nulls? Check if a "String resource" is being used in the project