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

Asked

Viewed 361 times

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 referring to this record (it is 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 is 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 database postgresql

  • A good programming practice is not to perform JOIN right in the clause FROM, and using explicitly the clause JOIN (SELECT ... FROM TabelaA ta JOIN TabelaB tb ON ta.Id = tb.Id JOIN ...). What result is you getting? Your query cannot return value in this field, or there will be no tuple referenced when tableA.tabled is NULL?

  • She made a cat with GROUP BY and it returns a row for each tableD record.

  • I still don’t understand anything. I would risk that the LEFT JOIN, as suggested by the Gypsy, is the solution you seek. However I could not understand your problem to help more.

  • No problem, his solution worked. : D

1 answer

3


What you want is a LEFT OUTER JOIN. There’s no need for the CASE precisely because the 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'

Browser other questions tagged sql database postgresql

You are not signed in. Login or sign up in order to post.