Compare tables and add result to row

Asked

Viewed 43 times

1

I have two tables

Table 1

N3_FILIAL | N3_CBASE | N3_ITEM | N3_CCDEPR

0N3_FILIAL  | N3_CBASE  | N3_ITEM   | N3_CCONTAB    | N3_CUSTBEM    | N3_VORIG1     | N3_TXDEPR1    | N3_VRDACM1
0001          BENF000001  0001        12301020014     12001002        491.810,00      10,0000         462.301,40

Table 2

N4_FILIAL | N4_CBASE | N4_ITEM | N4_CONTA

N4_FILIAL   | N4_CBASE  | N4_ITEM   | N4_CONTA
0001          BENF000001  0001        12301020014
0001          BENF000001  0001        12301020014
0001          BENF000001  0001        12301020014
0001          BENF000001  0001        12301020014
0001          BENF000001  0001        12301020014

The values are the same, however, in table 1 I have only one row and in 2 more than 1.

I wonder how I can mount a query to search in table 2 this condition (0001 | BENF000001 | 0001 | 12301020014) and the front of each record found in table 2 I add the rest of table 1 row. (N3_CUSTBEM | N3_VORIG1 | N3_TXDEPR1 | N3_VRDACM1)

Thank you very much!

sql database

  • Hello @Gustavo. What is the goal? If you want to address the values of Tabela 1 and can connect the two tables by these fields you have described, you do not need to have this information in the Tabela 2!

  • I don’t know if I understand your question correctly but it seems to me that with the GROUP BY clause plus an appropriate aggregation function, like the Postgresql string_agg, I can answer it.

  • @Gustavo Freire , show how you need data to be displayed.

  • @Motta I need that when finding the criterion (0001 | BENF000001 | 0001 | 12301020014) in table 2 he write as follows in TABLE 2: 0001 | BENF000001 | 0001 | 12301020014 + N3_CUSTBEM VALUES | N3_VORIG1 | N3_TXDEPR1 | N3_VRDACM1) of table 1

  • I did not understand why the Join does not solve , show in the question how you need the display , I confess not to have understood.

1 answer

0

I think the query below returns what you want:

SELECT      DISTINCT 
            T2.*
        ,   T1.N3_CUSTBEM
        ,   T1.N3_VORIG1
        ,   T1.N3_TXDEPR1
        ,   T1.N3_VRDACM1
FROM        T2
INNER JOIN  T1  ON  T1.N3_FILIAL    = T2.N4_FILIAL
                AND T1.N3_CBASE     = T2.N4_CBASE
                AND T1.N3_ITEM      = T2.N4_ITEM
                AND T1.N3_CCONTAB   = T2.N4_CONTA

You can validate the scenario in DB Fiddle (is built to Mysql, but the syntax works for any DBMS).

Browser other questions tagged sql database

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