GROUP BY with 2 tables

Asked

Viewed 1,443 times

2

I need to make a select with JOIN in two tables but I am not able to group to not repeat the lines. Follows the expression SQL:

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 'Progress.Key' column is invalid in the selection list because it is not contained in an aggregation function or clause GROUP BY

sql sql-server tsql

  • has already tried to put, GROUP BY Andamento.Numeroprocesso, Andamento.Chave ?

  • The solution will vary according to the relationship and data of these tables, try to include in the question the expected result. But one thing is certain the group by syntax should contain all fields that are not continuous in an aggregation function.

2 answers

0


I changed my logic to select so:

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 only of Process Numero without repetition, and then via code C# I get the data relating to the list of Process Numero.

  • Unless mistaken on my part, a process may or may not have progress. For example, new processes may not yet have progress releases, right? In this way, it seems to me that the correct junction is FROM Processo left join Andamento on .... // If you mount the joint correctly, the GROUP BY clause is not required.

0

Repeated lines, when not the expected result, usually occur when the junction 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 should be of the type LEFT OUTER JOIN.

Evaluate:

-- 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)
           );

Browser other questions tagged sql sql-server tsql

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