GROUP BY with 2 Tables

Question

GROUP BY with 2 Tables

Navigation

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

2

I need to do a select with JOIN on two tables but I can not group to not repeat the rows. Here is the SQL expression:

SELECT 
       Andamento.NumeroProcesso, 
       Andamento.Chave, 
       Andamento.Codigo, 
       Andamento.DataAnda, 
       Andamento.Andamento, 
       Andamento.AdvogadoID

FROM   Andamento FULL OUTER JOIN Processo 

ON     Andamento.NumeroProcesso = Processo.NumeroProcesso

WHERE  (Processo.Irrecuperavel = 1) AND (DATEDIFF(MONTH, 
       Andamento.DataAnda,GETDATE()) >= 6)

When I try the clause:

GROUP BY Andamento.NumeroProcesso

I get the following error:

The column 'Progress.Chave' is invalid in the selection list because it is not contained in an aggregate function or in the GROUP BY

sql sql-server tsql

asked by anonymous 24.11.2017 / 13:54

2 answers

0

Repeated lines, when they are not the expected result, usually occur when the join is incorrect or incomplete. If a process can have none, one or more progress information, it seems to me that the relationship between the Process and Progress tables must be of the LEFT OUTER JOIN type.

Rate it:

-- código #1 v2
SELECT 
       P.NumeroProcesso, 
       A.Chave, 
       A.Codigo, 
       A.DataAnda, 
       A.Andamento, 
       A.AdvogadoID

FROM   Processo as P
       left join Andamento as A on P.NumeroProcesso = A.NumeroProcesso

WHERE  P.Irrecuperavel = 1 
       and (A.NumeroProcesso is null
            or A.DataAnda < dateadd(month, -6, current_timestamp)
           );

24.11.2017 / 14:43

How to make a Docker image of a Node app that uses private repo that requires ssh Read a file in php

score 0 · Accepted Answer

I changed my logic to make the selected statement like this:

SELECT       
        Andamento.NumeroProcesso

FROM    Andamento LEFT OUTER JOIN Processo 

ON      Andamento.NumeroProcesso = Processo.NumeroProcesso
WHERE
    (Processo.Irrecuperavel = 1) AND 
    (DATEDIFF(MONTH, Andamento.DataAnda, GETDATE()) >= 6)

GROUP BY Andamento.NumeroProcesso

Where I get a list of only ProcessNumber without repetition, and then via C # code I get the data relating to the ProcessNumber list.