SQL data counts filtered by multiple tables

Question

SQL data counts filtered by multiple tables

Navigation

#1 by (1 votes)

1

I have a page in PHP where I have to generate a chart of "Registered sellers" filtered by region.

The problem is that the Sellers table has City Id, the City table has the State Id, and the State Id has the Id. And I need to filter the sellers by region .

Following structures:

Sellers table

City Table
StateTable
RegionTable

sql sql-server

asked by anonymous 14.04.2016 / 22:34

1 answer

Doubts question and answer systems with $ _GET method problem with "inspect element"

score 1 · Accepted Answer

Basically, a large JOIN solves everything:

select v.PrimeiroNome as "Nome do Vendedor", r.Nome as "Região"
from Vendedores v
inner join Cidades c on c.Id = v.IdCidade
inner join Estados e on e.Id = c.IdEstado
inner join Regionais r on r.Id = e.IdRegional;

To count:

select tabela.Nome as "Região", count(*) from
    (select v.PrimeiroNome as "Nome do Vendedor", r.Nome -- as "Região"
    from Vendedores v
    inner join Cidades c on c.Id = v.IdCidade
    inner join Estados e on e.Id = c.IdEstado
    inner join Regionais r on r.Id = e.IdRegional) tabela
-- group by "Região"; -- Isto aqui deu erro no Browser.
group by tabela.Nome