Find difference between two tables

Question

I wanted to mount an sql statement that returns differences between two tables.

For example, I have the table arquivos and the table conferencias where it has equal fields called titulo and valor, If in some title there is a different value 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

On the table arquivos the title number comes with an extra number at the end, so it has some way of considering only the first three digits?

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

Answer 1

Making a INNER JOIN, you can return the value difference by comparing the titulo and the very valor in both tables.

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

As in your table ARQUIVOS the field titulo comes with an extra digit, you could do the SELECT using the function substr of MySQL:

SELECT * FROM ARQUIVOS a
INNER JOIN CONFERENCIAS b ON SUBSTR(a.titulo, 1, 3) = b.titulo AND a.valor <> b.valor