Help on select to bring field even though null

Question

Help on select to bring field even though null

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (0 votes)

2

I need help to create a select, I have 4 tables (commission, person, request, request). So I need to bring all people and their goal values even though I do not have values yet in order. I made a select problem that it does not return the person when it has no request to that person.

I want it to return for example: name of person / goal / valuereached

My code is like this until now:

SELECT
  [PESSOA].[Fantasia] AS PESSOA,
  [METAS].[MetaValorMinimoBase] AS META,
  SUM(ISNULL(PDV_PedidoItemValorTotal,0)) AS Valor


FROM COM_METAS
  FULL JOIN PESSOA ON METAS.MetaRepCod = PESSOA.Codigo
  LEFT JOIN PEDIDO ON PESSOA.Codigo = PEDIDO.PedidoRepresentante
  LEFT JOIN PEDIDOITEM ON PEDIDO.PedidoCodigo = PEDIDOITEM.PedidoCodigo

WHERE PEDIDO.PedidoExcluido = 'N' and
PEDIDO.PedidoTipoMovimentoCodigo IN (1,5,6) AND
PEDIDO.PedidoSituacao IN ('A','B','O','T','E')AND
PEDIDO.PedidoDataEmissao BETWEEN '01/06/2018'  AND '05/06/2018'

GROUP BY
  [PESSOA].[Fantasia],
  [METAS].[MetaValorMinimoBase]

Order by [PESSOA].[Fantasia]

RETURN:

JOÃO      |60000    |697569
PEDRO     |240000   |1374417
MARIA     |60000    |67995
FRANCISCO |200000   |2376976
ZÉ        |NULL     |23423
ROMARIO   |20000    |NULL

I want you to bring me other people who have goals even if they have no value.

It even returns people with a null goal, but I want them to return those with null value or 0 as well.

sql-server

asked by anonymous 05.06.2018 / 21:34

3 answers

Doubt over Recursion in a power method Preventing anchored links from leaving stored histories

score 2 · Answer 1

You need to use LEFT JOIN instead of INNER JOIN , so even if there are no records in the table the result will be listed. It will also be necessary to reorganize your query so that the table with the most important data, in your case PESSOA is the basis of the query:

SELECT pes.EMP_Fantasia AS PESSOA,
       m.COM_MetaValorMinimoBase AS META,
       SUM(ISNULL(pi.PDV_PedidoItemValorTotal, 0)) AS Valor
  FROM dbo.PESSOA pes dbo.PEDIDO p
  LEFT JOIN PEDIDO p ON p.PDV_PedidoRepresentante = pes.EMP_Codigo
  LEFT JOIN dbo.PEDIDOITEM pi ON p.PDV_PedidoCodigo = pi.PDV_PedidoCodigo
  FULL JOIN dbo.METAS ON pes.EMP_Codigo = m.COM_MetaRepCod
 WHERE p.PedidoSituacao IS NULL
    OR (p.PedidoExcluido = 'N'
   AND p.PedidoTipoMovimentoCodigo IN (1,5,6)
   AND p.PedidoSituacao IN ('A','B','O','T','E')
   AND p.PedidoDataEmissao BETWEEN '01/06/2018' AND '05/06/2018')
 GROUP BY p.EMP_Fantasia,
          m.COM_MetaValorMinimoBase

score 1 · Answer 2

You can do this using left join

See that people have been selected and made a left join so that it does not even have requests, in sum was also added a checker that if null, will bring the value 0

SELECT
  [PESSOA].[Fantasia] AS REPRESENTANTE,
  [METAS].[MetaValorMinimoBase] AS META,
  SUM(ISNULL(PDV_PedidoItemValorTotal,0)) AS Valor
FROM COM_METAS
  FULL JOIN PESSOA ON METAS.MetaRepCod = PESSOA.Codigo
  LEFT JOIN PEDIDO ON PESSOA.Codigo = PEDIDO.PedidoRepresentante
  LEFT JOIN PEDIDOITEM ON PEDIDO.PedidoCodigo = PEDIDOITEM.PedidoCodigo
WHERE (1=1)
    AND ISNULL(PEDIDO.PedidoExcluido, 'N') = 'N' 
    AND ISNULL(PEDIDO.PedidoTipoMovimentoCodigo, 1) IN (1,5,6) 
    AND ISNULL(PEDIDO.PedidoSituacao, 'A') IN ('A','B','O','T','E')
    AND ISNULL(PEDIDO.PedidoDataEmissao, ''01/06/2018'') BETWEEN '01/06/2018'  AND '05/06/2018'
GROUP BY
  [PESSOA].[Fantasia],
  [METAS].[MetaValorMinimoBase]
Order by [PESSOA].[Fantasia]

I'd recommend reading: What is the difference between INNER JOIN and OUTER JOIN?

score 0 · Answer 3

Resolved!

I removed the Where and made the condition direct in the Join and added a OR in the join with the Pessoa table to bring me the names when it has both relation to the goal and the request.

<pre>SELECT

  PESSOA.Fantasia AS REPRESENTANTE,
  METAS.MetaValorMinimoBase AS META,
  COALESCE(SUM(PEDIDOITEM.PedidoItemValorTotal),0) AS Valor
 
FROM PEDIDO
  INNER JOIN PEDIDOITEM ON PEDIDO.PedidoCodigo = PEDIDOITEM.PedidoCodigo
AND 
PEDIDO.PedidoExcluido = 'N' and
PEDIDO.PedidoTipoMovimentoCodigo IN (1,5,6) AND
PEDIDO.PedidoSituacao IN ('A','B','O','T','E')AND
PEDIDO.PedidoDataEmissao BETWEEN '01/06/2018'  AND '05/06/2018'
  FULL JOIN METAS ON PEDIDO.PedidoRepresentante = METAS.MetaRepCod
  LEFT JOIN PESSOA ON  METAS.MetaRepCod = PESSOA.Codigo or
  PEDIDO.PedidoRepresentante = PESSOA.Codigo



GROUP BY
  PESSOA.Fantasia,
  METAS.MetaValorMinimoBase,

Order by PESSOA.Fantasia</pre>