CODEWITHSUNDEEP
mysqlsql
cool_cs
cool_cs

Reputation: 1751

Comparing two numbers that are approximately equal

I have two tables, Table A and Table B. I have two attributes L1 and L2 for each table. I am trying output all the rows for both tables where L1 and L2 are equal for both tables. The problem is that L1 an L2 may differ my some small quantity. 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 though there are records that do match. How do I resolve this problem?

Example:

L1 = 118.4363 for Table A but for Table B L1 = 118.445428

Upvotes: 11

Views: 9591

Answers (5)

DavidC.
DavidC.

Reputation: 677

In order to @cheeken's answer to work, you must put a semicolon at the last query, otherwise it won't work:

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

Upvotes: -1

Mike
Mike

Reputation: 26

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

mike

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270775

You will need to devise some tolerance, like say a difference of 0.01. Then compute the absolute value of the two when subtracted and see if it's 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;

Upvotes: 6

Nicol&#225;s Torres
Nicol&#225;s Torres

Reputation: 1345

You cannot ask the engine to return the ones which differ in "some small quantity".

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

Like rows where a-b > 5 or a - b > x and a - b < x+10. for example

Upvotes: 2

cheeken
cheeken

Reputation: 34685

Instead of checking for equality, check that the difference is below some threshold (e.g., 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

Upvotes: 20

Related Questions