I'm doing a query query, based on a table.
In this table (tableA) I bring several records from other tables, with some codes but one in particular is giving me a certain headache.
There is a record in tableA that can be NULL and in this case the query does not need to fetch the name for that record (it's a code), I tried with CASE in WHERE and SELECT and the query returned several repeated lines, I added GROUP BY, but it did not help much.
Currently the query looks like this.
SELECT
tableA.numero,
tableA.valor,
tableA.codigo,
tableB.nome,
tableC.nome,
tableD.nome = CASE
WHEN tableA.tabled IS NULL THEN 'NULL'
ELSE tableD.nome
END,
tableE.nome
FROM tableA,
tableB,
tableC,
tableD,
tableE
WHERE tableA.data = now()
AND tableA.ativo = '1'
AND tableA.tableb = tableB.codigo
AND tableA.tablec = tableC.codigo
AND CASE
WHEN tableA.tabled IS NOT NULL THEN tableA.tabled = tableD.codigo
ELSE 1 = 1
END
AND tableA.tabled = tabled.codigo