Relate sub query table with main query

Navigation
1

SGDB: MySql.

PROBLEM: I need to somehow relate the subquery (being table and field highlighted in Functional.codZone1) to the table "Zone", which is in the main query. Is there any way?

"ZONE" table: Contains all Zones of the city.

Table Fields: "code" (INT - Key - autoincrement - table identifier code), nameName (VARCHAR - name of the Zone)

Data:  Code | NameName 

1 | Zona Centro-Oeste 
2 | Zona Centro-Sul 
3 | Zona Leste 
4 | Zona Norte 
5 | Zona Oeste 
6 | Zona Sul

Tables "ITINERARY", "ITINERARIODEPENDENTE", "ITINERARIOEXTRA" (ALL SAME): Contains list of employees and areas where they live.

Table Fields: "Code" (INT - Key - autoincrement), "Employee code" (INT - Employee code), "codZone1" (INT - Area code where the employee is allocated)

Data: Code | Contact Us | codZona1 

1 | 1 | 5 
2 | 2 | 1
3 | 3 | 4
4 | 4 | 3
5 | 5 | 5
6 | 6 | 6
7 | 7 | 2
8 | 8 | 4
9 | 9 | 5

The query below tries to display the amount of "Codunctional" (Made by COUNT) existing in the tables "ITINERARY", "ITINERARIODEPENDENTE" and "ITINERARIOEXTRA" that are being unified by UNION, being summed in a Sub Query in the "SELECT I have a list of all of the tables in the table, and I have a list of all the tables in the table. ITINERARYEXTRA "by the fields" codZona1 "with the" code "field of the" Zone "table. 

    SELECT Zona.nomeZona, ( 
        SELECT SUM(qtdPessoas) AS ttPessoas FROM (
            SELECT      COUNT(DISTINCT Itinerario.codFuncionario) AS qtdPessoas
            FROM        Itinerario
            WHERE       Itinerario.codZona1 = Zona.codigo  
            UNION ALL
            SELECT      COUNT(DISTINCT ItinerarioDependente.codDependente) AS qtdPessoas
            FROM        ItinerarioDependente
            WHERE       ItinerarioDependente.codZona1 = Zona.codigo  
            UNION ALL
            SELECT      COUNT(DISTINCT ItinerarioExtra.codExtra) AS qtdPessoas
            FROM        ItinerarioExtra 
            WHERE       ItinerarioExtra.codZona1 = Zona.codigo)
        AS tbP1) AS QtdFuncionarios
    FROM Zona

Because the way I'm doing, it looks like this: 

Zona Centro-Oeste   900
Zona Centro-Sul     900
Zona Leste          900
Zona Norte          900
Zona Oeste          900
Zona Sul            900

Instead of coming like this: 

Zona Centro-Oeste   100
Zona Centro-Sul     200
Zona Leste          100
Zona Norte          400
Zona Oeste          50
Zona Sul            50
    
asked by anonymous 15.04.2014 / 17:01

1 answer

1
 SELECT Zona.nomeZona, ( 
        SELECT SUM(qtdPessoas) AS ttPessoas FROM (
            SELECT      COUNT(DISTINCT Itinerario.codFuncionario) AS qtdPessoas,
                        Itinerario.codZona1 
            FROM        Itinerario
            GROUP BY       Itinerario.codZona1  
            UNION ALL
            SELECT      COUNT(DISTINCT ItinerarioDependente.codDependente) AS qtdPessoas,
                        ItinerarioDependente.codZona1
            FROM        ItinerarioDependente
            GROUP BY  ItinerarioDependente.codZona1
            UNION ALL
            SELECT      COUNT(DISTINCT ItinerarioExtra.codExtra) AS qtdPessoas,
                        ItinerarioExtra.codZona1
            FROM        ItinerarioExtra 
            GROUP BY       ItinerarioExtra.codZona1)
        AS tbP1
        WHERE tbP1.codZona1 = Zona.codigo) AS QtdFuncionarios
    FROM Zona

As our friend said above would be so the query;

    
15.04.2014 / 23:20
Where do I find DLL microsoft.expression.encoder.devices? Datatables - Rowspan in tbody: Can not read property 'className' of undefined