MySQL "Left Outer Join" Issue

Question

Big Issue in "Left Outer Join"

"mydata" table schema pk1 as varchar , pk2 as varchar,pk3 as datatime, value1 as int,value2 as int

Left Outer Join SQL

select a.pk1,a.pk3,a.value1,a.value2,b.value1,b.value2 
from mydata as a 
left outer join mydata as b 
    on (a.pk1=b.pk1 and b.pk2='data2' and a.pk3=b.pk3) 
where a.pk1='abc' and a.pk2='data1' and a.pk3>='2016/5/3 15:00' and
    a.pk3 <= '2016/5/3 16:00'

it's OK when pk3 use >= & <= , select result

abc ,2016/5/3 15:00 , 1,1,2,2 
abc ,2016/5/3 15:30 , 2,2,9,9 
abc ,2016/5/3 16:00 , 3,3,12,12

but when i use a.pk3='2016/5/3 15:00', select result

abc ,2016/5/3 15:00 , 1,1,null,null

i try it at MS SQL, select result

abc ,2016/5/3 15:00 , 1,1,2,2

what's the different ??? and how should i fix MySQL sql stirng as i need the result which MS SQL does??

======================

CREATE TABLE `mydata` (
`pk1` char(20) NOT NULL,
`pk2` char(10) NOT NULL,
`pk3` datetime NOT NULL,
`value1` int NOT NULL,
`value2` int NOT NULL,
 PRIMARY KEY (`pk1`,`pk2`,`pk3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

by the way ,if i use a.pk3>='2016/5/3 15:00' and a.pk3 <= '2016/5/3 15:00' , and the select result

abc ,2016/5/3 15:00 , 1,1,2,2

========================

select a.pk1,a.pk3,a.value1,a.value2,b.value1,b.value2 
from mydata as a 
left outer join mydata as b 
    on (a.pk1=b.pk1 and b.pk2='data2' and a.pk3=b.pk3) 
where a.pk1='abc' and a.pk2='data1' and a.pk3>='2016/5/3 15:00' 

abc ,2016/5/3 15:00 , 1,1,2,2 
abc ,2016/5/3 15:30 , 2,2,9,9 
abc ,2016/5/3 16:00 , 3,3,12,12 
....
....

=========================

yes, fix sql string to a.pk3=convert('2016/5/1 09:30', datetime) will solve this problem.

Your result is confusing me. If the WHERE clause passed for that record, you should ideally be getting the same values from mydata as with the first clause. Can you include a SQL Fiddle with actual data so we may take a closer look? — Tim Biegeleisen

Quasimodo's clone Quasimodo's clone · Accepted Answer · 2016-05-03T05:58:14

Since you don't provide more code and the inserted data, we can only guess:

The timestamps in your table might be some seconds or milliseconds later. It would match the clause a.pk3 >= '2016/5/3 15:00' AND a.pk3 <= '2016/5/3 16:00' but isn't equal to the first value. In your display format the seconds and milliseconds are just cut off, but comparison depends on content, not what's displayed.

MySQL "Left Outer Join" Issue

2 Answers