Problems with LEFT JOIN in MySQL

I have a little problem with my MySQL que开发者_如何学Pythonry. I'm using LEFT JOIN to table1 and table2. But many rows in table2 can match one row in table1. I know that LEFT JOIN stop searching when it finds one match row, but it seems to do this randomly. Even when I sort table2 it doesn't take first match row.

Do you know maybe a solution?

Example:

table1

date1      |
-----------
2010-10-10 |
2010-10-10 |
2010-10-10 |

table2

date2      | item
-------------------------
2010-10-09 | item1
2010-10-08 | item2
2010-10-07 | item3

SQL query

SELECT * FROM table1 LEFT JOIN table2 ON date2<=date1

I expect

date1      | date2       | item
--------------------------------------
2010-10-10 | 2010-10-09  | item1
2010-10-10 | 2010-10-09  | item1
2010-10-10 | 2010-10-09  | item1

but I'm getting f.e

date1      | date2       | item
--------------------------------------
2010-10-10 | 2010-10-09  | item1
2010-10-10 | 2010-10-08  | item2
2010-10-10 | 2010-10-09  | item1

I know that LEFT JOIN stop searching when it finds one match row

Left join doesn't do this at all. It returns all matching rows. If you're having behavior you can't explain posting the entire query will help us help you.

Your sample data isn't very easy to work with as there is no PK on table1 and you have duplicates so GROUP BY can't be used.

Here's an approach that seems to work though I'd probably never use this in practice!

create table table1 (date1 DATE);

create table table2(date2 DATE, item varchar(10));

insert into table1
SELECT CAST('2010-10-10' AS DATE) AS date1 UNION ALL
SELECT CAST('2010-10-10' AS DATE) AS date1 UNION ALL
SELECT CAST('2010-10-10' AS DATE) AS date1;

insert into table2
SELECT CAST('2010-10-09' AS DATE) AS date2, 'item1' AS item  UNION ALL
SELECT CAST('2010-10-08' AS DATE) AS date2, 'item2' AS item  UNION ALL
SELECT CAST('2010-10-07' AS DATE) AS date2, 'item3' AS item;


SELECT 
      date1,
      cast(left(Top,10) as date) AS date2,
      cast(substring(Top,11) as char) AS item      
FROM
(
SELECT date1, 
      (SELECT CONCAT(date2,item) FROM table2 WHERE date2<=date1 ORDER BY date2 DESC LIMIT 1) AS Top
FROM table1
) t