How to make condition in a UNION result?

Question

EXPLANATION: I'm doing a UNION search, it's working correctly. The problem is to make a new filter on the last WHERE. It is not being applied as can be seen in the image.

CODE:

SELECT * FROM 
(
        SELECT
            locacoes.id as id,
            locacoes.chave as chave,
            'L' AS tipo,
            locacoes.codigo as codigo,
            locacoes.localRetirada as localRetirada,
            locacoes.clienteID as clienteID,
            locacoes.dataSaida as dataSaida,
            locacoes.dataChegada as dataChegada,
            locacoes.veiculo as veiculo,
            locacoes.situacao as situacao,
            locacoes.historico AS historico,
            veiculos.placa as placa,
            veiculos.marca as marca,
            veiculos.modelo as modelo
        FROM
            locacoes
            LEFT JOIN veiculos ON veiculos.id = veiculo
        WHERE
            locacoes.chave = '1323122'
    UNION
        SELECT
            contratos.id AS id,
            contratos.chave AS chave,
            'C' AS tipo,
            contratos.codigo AS codigo,
            contratos.localRetirada AS localRetirada,
            contratos.clienteID AS clienteID,
            contratos.dataIni AS dataSaida,
            contratos.dataFim AS dataChegada,
            contratos.veiculo as veiculo,
            contratos.status AS situacao,
            contratos.historico AS historico,
            veiculos.placa as placa,
            veiculos.marca as marca,
            veiculos.modelo as modelo
        FROM
            contratos
            LEFT JOIN veiculos ON veiculos.id = veiculo
        WHERE
            contratos.chave = '1323122' 

) AS derived_table

    WHERE
        dataSaida <= '2022-02-07 13:51:00' AND dataChegada >= '2022-02-07 13:51:00' 
        AND placa = 'GEC7I31'
        OR (`historico` IS NULL) ORDER BY historico asc

Where am I wrong?

The AND operator has higher precedence than OR, so your condition is equivalent to: `(dataSaida <= '2022-02-07 13:51:00' AND dataChegada >= '2022-02-07 13:51:00' AND placa = 'GEC7I31') OR (historico IS NULL)`. Is this that you want? — forpas, Feb 16 '22 at 19:31
Please do not post the result in an image, but as text. (the image is missing right now anyway...) — Luuk, Feb 16 '22 at 19:31
@forpas on second thought, I believe that `(historico IS NULL)` will be dealt with within php. — Tiago, Feb 16 '22 at 19:41
@forpas I would only do this condition `dataSaida <= '2022-02-07 13:51:00' AND dataChegada >= '2022-02-07 13:51:00' AND placa = 'GEC7I31'` — Tiago, Feb 16 '22 at 19:42
There is the registry, but this search does not work. `dataSaida <= '2022-02-07 13:51:00' AND dataChegada >= '2022-02-07 13:51:00'` — Tiago, Feb 16 '22 at 19:44
@forpas SORTED OUT. It was a problem of logic. I did that `IF(locacoes.dataChegada IS NULL, locacoes.dataPrevista, locacoes.dataChegada) as dataChegada,` and it solved — Tiago, Feb 16 '22 at 20:04

How to make condition in a UNION result?

0 Answers0