Problems with LEFT JOIN in MySQL

0
votes

I have a little problem with my MySQL query. 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
2
databasemysql
"I know that LEFT JOIN stop searching when it finds one match row, but it seems to do this randomly" This is incorrect. Are you using GROUP BY? That might explain an undeterministic result in MySQL What is your exact query? - Martin Smith
Post your query and expected output. Also provide some sample data. - D'Arcy Rittich
"I know that LEFT JOIN stop searching when it finds one match row, but it seems to do this randomly." No - it does not. What you describe fits how EXISTS works... - OMG Ponies
Your sample data still looks incorrect. Did you test it? I would have expected 9 rows returned. This is a cartesian JOIN with the data that you have. - Martin Smith
Ok, I've made a mistake. You have right that LEFT JOIN don't stop searching when it finds one match row. But now i think you can see what I want to get. - Mateusz

2 Answers

0
votes

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.

0
votes

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