Find difference between two tables

Navigation
0

I wanted to set up an sql statement that would return differences between two tables.

For example, I have the arquivos table and the conferencias table where you have equal fields called titulo and valor , if in any title different value appears compared to the two tables, I need to treat this as a difference . 

    ARQUIVOS        | CONFERENCIAS
    ID TITULO VALOR | ID TITULO VALOR  
    1  0018    12    | 1  001    12  
    2  0026    20    | 2  002    20  
    3  0032    50    | 3  003    48 
    4  0047    120   | 4  004    120

In the table arquivos the title number comes with an extra number at the end, so is there any way to consider only the first three digits?

In the above example the difference is in the title 003 ie the value of the difference would be 2.

    
asked by anonymous 23.09.2017 / 14:48

1 answer

1

By doing a INNER JOIN , you can return the value difference by comparing the titulo and the valor itself in the two tables. 

SELECT * FROM ARQUIVOS a
INNER JOIN CONFERENCIAS b ON a.titulo = b.titulo AND a.valor <> b.valor

Because in your% wrapper% field the ARQUIVOS field comes with one more digit, you could do titulo using the SELECT of substr : 

SELECT * FROM ARQUIVOS a
INNER JOIN CONFERENCIAS b ON SUBSTR(a.titulo, 1, 3) = b.titulo AND a.valor <> b.valor
    
23.09.2017 / 14:55
Date in Portuguese returning in English Template Inheritance is not working