Always include records from another table if the value of the related field is not NULL

Question

Always include records from another table if the value of the related field is not NULL

Navigation

#1 by (3 votes)

1

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

sql postgresql

asked by anonymous 09.12.2014 / 19:59

1 answer

How to get the value dynamically from a select Function that returns pointer crashes when it tries to return pointer to main program

score 3 · Accepted Answer

What you want is a LEFT OUTER JOIN . You do not need CASE just because LEFT OUTER JOIN brings the record even if the association does not exist.

SELECT
   tableA.numero,
   tableA.valor,
   tableA.codigo,
   tableB.nome,
   tableC.nome,
   tableD.nome,
   tableE.nome
FROM tableA
INNER JOIN tableB ON tableA.tableb = tableB.codigo
INNER JOIN tableC ON tableA.tablec = tableC.codigo
LEFT OUTER JOIN tableD ON tableA.tabled = tableD.codigo
INNER JOIN tableE ON tableA.tablee = tableE.codigo
WHERE tableA.data = now()
  AND tableA.ativo = '1'