Join With Next date less than

Alright this is a little hard to explain, but I have two tables of very
different data. Both tables have a date timestamp. The timestamps are not
going to match, but I need to join table2 on to table1 via the next date
equal to or less than.

So..

Table1

|Date|id|
|2010/10/22 13:55:20|10
|2010/10/22 13:56:18|11

Table2
|Date2|id2|
|2010/10/22 13:55:19|A
|2010/10/22 13:57:30|B

I want to join table2 onto table1 where the date is equal to or less than
the date but limit it to one per row in table1.

In the above example I would end up with

|2010/10/22 13:55:20|10|2010/10/22 13:55:19|A
|2010/10/22 13:56:18|11|2010/10/22 13:55:19|A

It would ignore the 2nd row in table2.

I know how to do a join in MySQL but am having some issues with knowing
if I can have mysql do the logic of joining the row in table2 equal to or
less than the date in table1 limit to 1 date.

Hope I am explain this well enough..

Thank you.

[smantscheff]

What you cannot do is use the limit clause in subqueries. This would be an obvious way to go, by MySQL does not yet support it.
Here is another way to do it:

drop table if exists t1;
drop table if exists t2;
create table t1 (id1 integer, date1 datetime);
create table t2 (id2 integer, date2 datetime);
insert into t1 values (10,'2010/10/22 13:55:20'), (11,'2010/10/22 13:56:18');
insert into t2 values (16,'2010/10/22 13:55:19'), (17,'2010/10/22 13:57:30');

select t1.*, t2.* 
from t1, t2
where t2.date2 = (select max(date2) from t2 where date2 <= date1)
;
+-----+---------------------+-----+---------------------+
| id1 | date1               | id2 | date2               |
+-----+---------------------+-----+---------------------+
|  10 | 2010-10-22 13:55:20 |  16 | 2010-10-22 13:55:19 |
|  11 | 2010-10-22 13:56:18 |  16 | 2010-10-22 13:55:19 |
+-----+---------------------+-----+---------------------+

Thank you for your reply. It didn't seem to do exactly what I wanted, but played with it some to limit a row per id of table 1. I will have to dig into it more when I get some time and make sure but am going with this for now.

SELECT * from t1 join t2 ON date2 = (select max(date2) from t2 where date2 <= date1) group by id1

[drjohn]

joins are made on data that is identical.

joins are made on data that is identical.

I see what you are saying but from changing the values in my tables this select is joining on the data I need that is closest to the time in table 1 with out going over and only matching for the number of rows in table 1.

I am open to any help on a better way to do this, or if you know of some issue that might come up from what I am doing?

[smantscheff]

This is IMHO only a fight about words. JOINs are intended to work for identical column content and can be shorthand for WHERE statements. In MySQL I cannot see any issues arising from using the one or the other.