Hi All,

I have a question for clearer brains than mine. I would like to join
two tables,. There may be many possible joins in table B to table A,
but I only want to join one row from B to table A - the row with the
closest, but lesser, date.


TABLE A
Row Id date
1 46 3 Jan
7 20 10 Jan

TABLE B
Row Id date
4 46 1 Jan
5 46 2 Jan
6 46 4 Jan
8 20 8 Jan
10 20 7 Jan
11 20 9 jan

Result
Row 1` in A is joined to row 5 in B
Row 7` in A is joined to row 11 in B

SELECT
a.row,
b.row
FROM
A a
LEFT JOIN B b
ON (a.id = b.id AND a.date > b.date AND <the row with the max dates
from the possible join in b>?)

Any suggestions would be appreciated.

Terry

This should be what you're looking for:

mysql> SELECT * FROM a;
+-----+----+------------+
| row | id | d |
+-----+----+------------+
| 1 | 46 | 2006-01-03 |
| 7 | 20 | 2006-01-10 |
+-----+----+------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM b;
+-----+----+------------+
| row | id | d |
+-----+----+------------+
| 4 | 46 | 2006-01-01 |
| 5 | 46 | 2006-01-02 |
| 6 | 46 | 2006-01-04 |
| 8 | 20 | 2006-01-08 |
| 10 | 20 | 2006-01-07 |
| 11 | 20 | 2006-01-09 |
+-----+----+------------+
6 rows in set (0.01 sec)

mysql> SELECT c.row as row_a, b.row as row_b, c.max_d
-> FROM b INNER JOIN
-> (SELECT a.row, max(b.d) as max_d
-> FROM a INNER JOIN b
-> ON a.id = b.id AND b.d < a.d
-> GROUP BY a.row) AS c
-> ON b.d = c.max_d;
+-------+-------+------------+
| row_a | row_b | max_d |
+-------+-------+------------+
| 1 | 5 | 2006-01-02 |
| 7 | 11 | 2006-01-09 |
+-------+-------+------------+
2 rows in set (0.01 sec)

Markus