Help select to bring field even if null

Question

I need help to create a select, I have 4 tables (commission, person, request, pedidoitem). So I need to bring in all the people and their goal values even if they don’t have values yet. I made a select the problem that it does not return the person when there is no request for this person.

I want it to return for example: person name/ meta/ value achieved

my code is like this so far:

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 meta null, but I want to return also those that have value (of the requests) null or 0 in the case.

Answer 1

You need to make use of the LEFT JOIN instead of INNER JOIN, 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 for consultation:

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

Answer 2

You can do it using the left join

See who was selected people and is made a left join so that it brings even having no orders, no sum 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 advise reading of: What is the difference between INNER JOIN and OUTER JOIN?

Answer 3

Solved!

I removed the Where and made the condition direct on Join and added a OR at the junction with the table Pessoa to bring me the names when it has as much to do with the goal as with 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>