How to join two tables based on a decimal column?-CodePudding

I found other posts where people asked about joining sql tables using decimal columns and the general answer is "try to avoid it". But here is a case that I just have to use some kind of a join (fuzzy join?) on decimal columns:

I have two tables, each has multiple columns and different number of rows.

Table A has 1000 rows. Table B has 10 rows.

Each table also has a column representing a time count from time zero as a fraction of an hour. E.g., a value of 1.5 represents an hour and a half after time 0. There could be up to 5 digits after the decimal point (or less).

I would like to do an outer fuzzy join on the decimal columns so that the each row in Table B will be aligned with the closest time point in Table A.

Any ideas how to achieve this?

CodePudding user response：

You can try something like this

Select * From Table a
inner join Table b 
on Abs(b.column - a.column) < 0.0000000001

Finding the least difference in the column values .You can define the length of decimal zeroes