Select only tuples from a table with JOIN

Asked

Viewed 211 times

1

I have the following query:

SELECT finpag.cod_forn 
      ,sicforn.nome
      ,finpag.data_pagto 
      ,finempe.cod_reduzido 
      ,finpag.num_empenho 
      ,finpag.ano_empenho 
      ,num_liquid 
      ,ano_liquid 
      ,valor_transacao 
 FROM finpag 
 INNER JOIN sicforn on 
       sicforn.cod_forn = finpag.cod_forn 
 INNER JOIN finempe on
       (finempe.num_empenho = finpag.num_empenho) 
 WHERE  finpag.data_pagto between '1-1-1997' and '31-12-1997' and finpag.cod_forn = 1840 and finpag.ano_empenho = 97
 ORDER BY finpag.num_liquid

That returns to me the following query:

cod_forn/nome/cod_reduzido/num_empenho/ano_empenho/num_liquid/ano_liquid/valor_transacao

1840    LUC'S ELETRODOMESTICOS LTDA 286 1473    97  3554    97  734
1840    LUC'S ELETRODOMESTICOS LTDA 3   1473    97  3554    97  734
1840    LUC'S ELETRODOMESTICOS LTDA 2   1661    97  3671    97  649
1840    LUC'S ELETRODOMESTICOS LTDA 370 1661    97  3671    97  649
1840    LUC'S ELETRODOMESTICOS LTDA 370 1661    97  3903    97  85
1840    LUC'S ELETRODOMESTICOS LTDA 2   1661    97  3903    97  85
1840    LUC'S ELETRODOMESTICOS LTDA 39  1712    97  3942    97  649
1840    LUC'S ELETRODOMESTICOS LTDA 3   1712    97  3942    97  649
1840    LUC'S ELETRODOMESTICOS LTDA 3   1712    97  4143    97  85
1840    LUC'S ELETRODOMESTICOS LTDA 39  1712    97  4143    97  85
1840    LUC'S ELETRODOMESTICOS LTDA 61  1889    97  3894    97  64
1840    LUC'S ELETRODOMESTICOS LTDA 348 1889    97  3894    97  64
1840    LUC'S ELETRODOMESTICOS LTDA 136 2165    97  4571    97  176
1840    LUC'S ELETRODOMESTICOS LTDA 10  2165    97  4571    97  176
1840    LUC'S ELETRODOMESTICOS LTDA 58  2010    97  4331    97  562
1840    LUC'S ELETRODOMESTICOS LTDA 61  2010    97  4331    97  562
1840    LUC'S ELETRODOMESTICOS LTDA 53  2286    97  5111    97  70
1840    LUC'S ELETRODOMESTICOS LTDA 264 2286    97  5111    97  70
1840    LUC'S ELETRODOMESTICOS LTDA 285 1919    97  5714    97  734
1840    LUC'S ELETRODOMESTICOS LTDA 64  1919    97  5714    97  734
1840    LUC'S ELETRODOMESTICOS LTDA 17  2534    97  5277    97  78
1840    LUC'S ELETRODOMESTICOS LTDA 78  2534    97  5277    97  78
1840    LUC'S ELETRODOMESTICOS LTDA 25  2928    97  6612    97  1080
1840    LUC'S ELETRODOMESTICOS LTDA 227 2928    97  6612    97  1080
1840    LUC'S ELETRODOMESTICOS LTDA 17  2773    97  6904    97  130,5
1840    LUC'S ELETRODOMESTICOS LTDA 10  2773    97  6904    97  130,5
1840    LUC'S ELETRODOMESTICOS LTDA 389 2513    97  6621    97  575
1840    LUC'S ELETRODOMESTICOS LTDA 348 2513    97  6621    97  575
1840    LUC'S ELETRODOMESTICOS LTDA 109 2965    97  6762    97  549
1840    LUC'S ELETRODOMESTICOS LTDA 53  2965    97  6762    97  549
1840    LUC'S ELETRODOMESTICOS LTDA 12  2994    97  6763    97  549
1840    LUC'S ELETRODOMESTICOS LTDA 188 2994    97  6763    97  549
1840    LUC'S ELETRODOMESTICOS LTDA 10  2773    97  6960    97  156,6
1840    LUC'S ELETRODOMESTICOS LTDA 17  2773    97  6960    97  156,6
1840    LUC'S ELETRODOMESTICOS LTDA 49  2657    97  6912    97  1500
1840    LUC'S ELETRODOMESTICOS LTDA 31  2657    97  6912    97  1500
1840    LUC'S ELETRODOMESTICOS LTDA 264 3630    97  7698    97  249
1840    LUC'S ELETRODOMESTICOS LTDA 3   3630    97  7698    97  249
1840    LUC'S ELETRODOMESTICOS LTDA 188 2994    97  7198    97  1389
1840    LUC'S ELETRODOMESTICOS LTDA 12  2994    97  7198    97  1389

I need to remove duplicates, as could be done (without using aggregation function)?

sql-server

  • if I understand correctly you have the expected return (conditions); then it would only be the case to control the fields you select, no?! You are bringing in finempe.cod_reduzido, simply remove it (and other fields from the table finempe be there)

  • the data are duplicated precisely because of finempe.cod_reduced (there is no cod_column reduced in finpag), as they are different in the table finempe and finpag, so I would like to select only the table finpag

  • Your question is somewhat confused, but as far as I understand it, add in the JOIN condition between the FINEMPE and FINPAG tables the cod_reduced field. {INNER JOIN FINEMPE ON (FINEMPE.NUM_EMPENHO = FINPAG.NUM_EMPENHO AND FINEMPE.COD_REDUZIDO = FINPAG.COD_REDUZIDO)}

  • @Andrey there is no cod_reduced column in finpag, if you look at the query I look for it from finempe

  • Then place the fields in a GROUP BY and make a COD_REDUZIDO MAX.

  • @Andrey you could exemplify in a reply? would facilitate my understanding

Show 1 more comment

2 answers

2

I believe the SQL below solves your problem: 

SELECT finpag.cod_forn 
          ,sicforn.nome
          ,finpag.data_pagto 
          ,MAX(finempe.cod_reduzido) AS cod_reduzido
          ,finpag.num_empenho 
          ,finpag.ano_empenho 
          ,num_liquid 
          ,ano_liquid 
          ,valor_transacao 
     FROM finpag 
     INNER JOIN sicforn on 
           sicforn.cod_forn = finpag.cod_forn 
     INNER JOIN finempe on
           (finempe.num_empenho = finpag.num_empenho) 
     WHERE  finpag.data_pagto between '1-1-1997' and '31-12-1997' and finpag.cod_forn = 1840 and finpag.ano_empenho = 97
  GROUP BY finpag.cod_forn 
          ,sicforn.nome
          ,finpag.data_pagto 
          ,finpag.num_empenho 
          ,finpag.ano_empenho 
          ,num_liquid 
          ,ano_liquid 
          ,valor_transacao 
     ORDER BY finpag.num_liquid

  • that’s right... but there were still some inconsistencies here... I think neither MIN nor MAX would serve, which I could put instead of them?

  • Can you explain the inconsistency? It can be any aggregation function, MIN, MAX, AVG, SUM... depends on your ultimate goal.

  • it actually removed the duplicates, but the logic of selecting the larger or smaller cod_reduced is not correct

  • Let me better understand your reality, is the finemp table your list of registered companies? If so, how many companies have registered? Maybe just apply a Where to your original SQL to delimit to bring only one record of the finemp table...

  • It is more or less there, this query returns the expenses incurred in with other companies but in the table finempe I do not have the name of the company ( picked up in the Inner Join with sicforn) and also did not have the reducedcod_ (caught in the Inner Join with finempe)

  • Please post the structure of the tables: finpag, finempe and sicforn... at least the keys in these tables

  • there are columns of tables

  • Let’s go continue this discussion in chat.

Show 3 more comments

1

Solution

In your case in question, probably the RIGHT JOIN which is what you need, because you only search for the correlated values that are in your "right table", that is, in your Join RIGHT.

Concept

Basically you have to understand the concept of left and right (left and right), for example:

Your table in FROM it is your left table, ie, LEFT, when making a JOIN.

FROM tabela1 X
LEFT OUTER JOIN tabela2 Y ON Y.campo1 = X.campo2

I mean, you’re bringing everything from table X, plus the Y records that link to X.

Now in:

FROM tabela1 X
RIGHT OUTER JOIN tabela2 Y ON Y.campo1 = X.campo2

You bring everything that is in your right table "Y" plus those of "X" that has to do with "Y".

The INNER only what is common in the 2 tables, and the FULL brings EVERYTHING independent of relationships.

There is a lot of material about this, and especially this super-known image that helps a lot in understanding:

tab

Edit

I need to remove duplicates, as could be done (without using aggregation function)?

With this quote after editing, it changes the context. In this case the GROUP BY

  • neither left nor rigth apply any changes in the result.

  • Now with that quote you made: Preciso remover as duplicatas, como poderia ser feito (sem utilizar função de agregação)? understood. But in this case only with the GROUP BY

  • as exemplified in the above answer?

  • As in Andrey’s answer. You have to know what you want to put together, and then define all the fields. If you’ve never done it before or still have some doubt, create a test bench, do it in parts. It’s easier to learn to drive in a car than a cart ! rsrs If you still have questions, put your bank on http://sqlfiddle.com/

  • i think you don’t understand my brm rsrs I would NOT like to use aggregation function, the group by comes just to use them, right?

  • Yes I understood ! rs That’s why I said Mas nesse caso somente com o GROUP BY ! The records come in your filter, to unite them only that way. I see no other way. Have you seen something that does the way you want it ? If you have examples you pass that we see !

Show 1 more comment

Browser other questions tagged sql-server

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