SELECT Union ALL Dismiss Arguments

Question

SELECT Union ALL Dismiss Arguments

Navigation

#1 by (4 votes)

3

I have this query that adds two results to different tables.

SELECT 
    sum(g) saldo_anterior 
from (
    SELECT 
        SUM(valor_pg) g 
    FROM ctrl_deposito 
    WHERE MONTH(data_pg) < 11 and
    YEAR(data_pg) <= 2016  or
    YEAR(data_pg) < 2016 and
    departamento = "BRADESCO"

    UNION ALL

    SELECT 
        SUM(valor) 
    FROM concessionarias 
    WHERE MONTH(data) < 11 and 
    YEAR(data) <= 2016  or 
    YEAR(data) < 2016 and 
    tipo = "BOLETO" OR 
    tipo = "COPASA" OR 
    tipo = "TELEFONE" OR 
    tipo = "BRADESCO"
) tabela_virtual

However, it is disregarding department parameters at the time of the query.

Ex: I have records in the mines where department = 'CEMIG' and no record with department = 'BRADESCO' and even then it is picking up the CEMIG records and adding the sum.

In individual queries (without using UNIONN ALL), it considers, but when you bind the results, all the DEPARTMENT AND TYPE parameters are dispensed and the table data is added together.

What can it be?

php mysql sql

asked by anonymous 17.07.2017 / 14:13

1 answer

Preloader with Materialize Initialize apache and mysql on windows 7 32 bits using Xampp

score 4 · Accepted Answer

Your problem is being OR within the query,

first that makes no sense YEAR(data) <= 2016 or YEAR(data) < 2016 , only YEAR(data) <= 2016 is equivalent to that, and then all AND operators are processed first than the OR operators causing them to return possible unwanted results.

The correct one would look like this:

SELECT 
    sum(g) saldo_anterior 
from (
    SELECT 
        SUM(valor_pg) g 
    FROM ctrl_deposito 
    WHERE MONTH(data_pg) < 11 and
    YEAR(data_pg) <= 2016 and
    departamento = "BRADESCO"

    UNION ALL

    SELECT 
        SUM(valor) 
    FROM concessionarias 
    WHERE MONTH(data) < 11 and 
    YEAR(data) <= 2016 and 
    (tipo = "BOLETO" OR 
    tipo = "COPASA" OR 
    tipo = "TELEFONE" OR 
    tipo = "BRADESCO")
) tabela_virtual

The operators OR put in parentheses, so that they are processed first, before the operators AND