Doubt QUERY SQL construction

Question

I have two TB1 and TB2 tables with equal fields in order to compare the records.

The logic is: Query the records contained in TB1, but not contained TB2, considering 3 fields: code, date and value. Because the code can repeat itself but date and value vary, so I need to compare the record as a whole.

Soon:

SELECT CODIGO, DATA, VALOR
FROM TB1
EXCEPT
SELECT CODIGO, DATA, VALOR
FROM TB2

However, I need to create a condition/clause so that it does not return records whose difference in value is = 1

TAB1:
Codigo: 001 Data: 02/02/2019 Valor: 224

TAB2:

Codigo: 001  Data: 02/02/2019 Valor: 223


Resultado: Não deve retornar nenhum registro. Pois 224 - 223 = 1.

Answer 1

   WITH CTE AS
 (
  SELECT TAB1.CODIGO,
   TAB1.DATA,
   TAB1.VALOR,
   TAB2.VALOR,
   (TAB1.VALOR - TAB2.VALOR) AS DIFERENCA
 FROM TAB1 INNER JOIN TAB2 
        ON TAB1.CODIGO = TAB2.CODIGO
 )
SELECT * FROM CTE 
    WHERE DIFERENCA <1;

Answer 2

I think my Query will be more complex than it looks. For all the SQL statements you sent me

I made 2 records in each table:

Tab1:
codigo  datap   valor
001 10/10/2010  800
001 10/10/2010  1000

Tab2:

codigo  datap   valor
001   10/10/2010    801
001   10/10/2010    999

He returned:

codigo  datap   valor
001 10/10/2010  801
001 10/10/2010  999

Could not have returned anything, since I only want the records that are in TB1 but are not in TB2

For you to understand me better:

I desire the records that are in TAB1, but are not in TAB2.

So imagine the following record:

Tab1:
codigo  datap   valor
001 10/10/2010  800
001 10/10/2010  900
001 10/10/2010  700


Tab2:

codigo  datap   valor
001   10/10/2010    801
001   10/10/2010.   950
001   10/10/2010.   701

Thus the correct would be to display the TAB1 record, since it is in TAB1, but it is not in TAB2 (divergent VALUE field).

However, I want to exclude from this condition the VALUE field whose difference between them is 1.

Therefore, for the above records, the expected output is:

001 10/10/2010  900

Explanation: In TB1 we have 800, but in TB2 we have 801. Difference 1.

The same thing for the 700.

Answer 3

See if it answers:

SELECT TB1.CODIGO, TB1.DATA, TB1.VALOR
FROM TB1 LEFT OUTER JOIN TB2 ON (TB1.CODIGO = TB2.CODIGO AND TB1.DATA =  TB2.DATA)
WHERE TB2.CODIGO IS NULL OR TB2.DATA IS NULL OR TB2.VALOR IS NULL OR (ABS(TB1.VALOR-TB2.VALOR) <> 1);

======================================

From the tests I’ve run on the data provided, it appears to be:

SELECT tab1.codigo, tab1.datap, tab1.valor
FROM tab1
WHERE NOT EXISTS (SELECT * FROM tab2 WHERE (tab1.codigo = tab2.codigo AND tab1.datap = tab2.datap AND ABS(tab1.valor - tab2.valor) = 1));

Answer 4

Updating

For his new explanation, the closest answer would be "anonymity". I would make just one adjustment, but it would be the same:

SELECT A.CODIGO, A.DATAP, A.VALOR 
FROM TB1 A
LEFT OUTER JOIN TB2 B ON (B.CODIGO = A.CODIGO AND B.DATAP = A.DATAP)
WHERE ((B.CODIGO OR B.DATAP OR B.VALOR) IS NULL) OR ABS(B.VALOR - A.VALOR) <> 1

See working: db-fiddle

---

I’ll do it another way to others to have more options and even sometimes help you understand better.

SELECT A.CODIGO, A.DATA, A.VALOR
FROM TB2 B
RIGHT OUTER JOIN TB1 A ON (A.CODIGO = B.CODIGO AND A.DATA =  B.DATA)
WHERE (NOT EXISTS (SELECT * FROM TB1) OR ABS(B.VALOR - A.VALOR) <> 1)

I didn’t test it, so if there’s something wrong, comment.

---

A brief explanation about Inner, Left, Right, Outer/Full and Cross Join

EXISTS / NOT EXISTS

ABS