MySQL JOIN two tables on closest matching value

Question

I have the following situation:

tableA
+-------+-------+
| id    | Value |
+-------+-------+
| 1     | 1000  |
| 2     | 20    |
| 3     | 62    |
| 4     | 0     |
+-------+-------+

tableB
+-------+--------+
| Value | Lookup |
+-------+--------+
|    10 | a      |
|    20 | b      |
|    30 | b      |
|    40 | g      |
|    50 | h      |
|    60 | f      |
|    70 | a      |
|    80 | a      |
|    90 | v      |
|   100 | b      |
+-------+--------+

And I need to return the lookup in table B that most closely matches the value field in table A. For example.

+-------+-------+--------+
| id    | Value | Lookup |
+-------+-------+--------+
| 1     | 1000  | b      |
| 2     | 20    | b      |
| 3     | 62    | f      |
| 4     | 0     | a      |
+-------+-------+--------+

How can I go about doing this?

Answer 1

Here is an option using joins:

SELECT
    a.Id, a.Value, b.Lookup
FROM tableA a
CROSS JOIN tableB b
INNER JOIN
(
    SELECT a.Id, MIN(ABS(a.Value - b.Value)) AS min_abs_value
    FROM tableA a
    CROSS JOIN tableB b
    GROUP BY a.Id
) t
    ON a.Id = t.Id AND
       ABS(a.Value - b.Value) = t.min_abs_value;

Demo

While this query does join to a subquery, the subquery is not correlated.