Comparing two numbers that are roughly equal

Question

I have two tables, table A and table B. I have two attributes L1 and L2 for each table. I am trying to print all rows for both tables, where L1 and L2 are equal for both tables. The problem is that L1 and L2 may differ from my small size. So when I run:

SELECT * FROM TableA l1 join TableB l2 on l1.L1 =l2.L1 and l1.L2 = l2.L2 

I get an empty set, even if there are records that match. How to solve this problem?

Example:

L1 = 118.4363 for table A, but for table B L1 = 118.445428

Answer 1

Instead of checking for equality, make sure that the difference is below a certain threshold (for example, 0.1, as in the example below).

 SELECT * FROM TableA l1, TableB l2 WHERE ABS(l1.L1-l2.L1) < 0.1 AND ABS(l1.L2-l2.L2) < 0.1 

Answer 2

You will need to work out some tolerance, for example, a difference of 0.01. Then calculate the absolute value of the two when subtracting and see if it is within your tolerance

 SET @tolerance_value = 0.01; SELECT * FROM TableA l1 join TableB l2 on ABS(l1.L1 - l2.L1) < @tolerance_value and ABS(l1.L2 - l2.L2) < @tolerance_value; 

Answer 3

You cannot ask the engine to return those that are "small."

You can select the rows, the difference "abs (a - b)" is between two fixed values.

Like lines where ab> 5 or a - b> x and a - b <x + 10 . eg

Answer 4

Try using the round function in Sybase or SQL Server, so 118 matches 118. For other DBMSs, find the round equivalent.

microphone

Answer 5

To answer @cheeken to work, you must put a semicolon in the last request, otherwise it will not work:

 SELECT * FROM TableA l1, TableB l2 WHERE ABS(l1.L1-l2.L1) < 0.1 AND ABS(l1.L2-l2.L2) < 0.1;