Select only tuples from a table with JOIN

Question

Select only tuples from a table with JOIN

Navigation

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

0

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

Which returns 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, how could it be done (without using aggregate function)?

sql-server

asked by anonymous 05.02.2018 / 19:26

2 answers

Select2 does not work on my pc but works on jsfidle How to execute event just after the $ ('div'). load (url)?

score 1 · Answer 1

I believe the following SQL will solve 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

score 1 · Answer 2

Solution

In your case, probably RIGHT JOIN is what you need, because it only looks 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, for example:

Your table in FROM is its left table, that is, LEFT , when it will make a JOIN .

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

That is, you are bringing everything from the X table, plus the Y records that have binding to the X.

Now in:

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

You put everything on your right-hand table "Y" plus those of "X" that have a relationship with "Y".

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

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

edit

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

With this quote after editing, the context changes. In this case you should use GROUP BY