mysql> show variables like 'version%'; +-------------------------+---------------------+ | Variable_name | Value | +-------------------------+---------------------+ | version | 10.0.17-MariaDB-log | | version_comment | MariaDB Server | | version_compile_machine | x86_64 | | version_compile_os | Linux | | version_malloc_library | bundled jemalloc | +-------------------------+---------------------+ 5 rows in set (0.00 sec) mysql> CREATE TABLE T1( -> K1 INT PRIMARY KEY, -> Name VARCHAR(15) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO T1 VALUES (1,'T1Row1'); Query OK, 1 row affected (0.01 sec) mysql> mysql> CREATE TABLE T2( -> K2 INT PRIMARY KEY, -> K1r INT, -> rowTimestamp DATETIME, -> Event VARCHAR(15) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO T2 VALUES -> (1, 1, '2015-04-13 10:42:11' ,'T1Row1Event1'), -> (2, 1, '2015-04-13 10:42:12' ,'T1Row1Event2'), -> (3, 1, '2015-04-13 10:42:12' ,'T1Row1Event3'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) mysql> SELECT -> t1a.*, -> t2a.*, -> t2b.K2 as K2B, -> t2b.K1r as K1rB, -> t2b.rowTimestamp as rowTimestampB, -> t2b.Event as EventB -> FROM -> T1 as t1a -> JOIN T2 as t2a -> ON t2a.K1r = t1a.K1 -> LEFT JOIN ( -> SELECT -> t2i.* -> FROM -> T1 as t1i -> LEFT JOIN T2 as t2i -> ON t2i.K1r = t1i.K1 -> WHERE -> t1i.K1 = 1 -> and t2i.K2 IS NOT NULL -> ) as t2b -> ON t2b.K1r = t1a.K1 -> AND t2b.rowTimestamp > t2a.rowTimestamp -> OR (t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2) -> WHERE -> t1a.K1 = 1 -> -- and t2b.K2 IS NULL -> -> ; +----+--------+----+------+---------------------+--------------+------+------+---------------------+--------------+ | K1 | Name | K2 | K1r | rowTimestamp | Event | K2B | K1rB | rowTimestampB | EventB | +----+--------+----+------+---------------------+--------------+------+------+---------------------+--------------+ | 1 | T1Row1 | 1 | 1 | 2015-04-13 10:42:11 | T1Row1Event1 | 2 | 1 | 2015-04-13 10:42:12 | T1Row1Event2 | | 1 | T1Row1 | 1 | 1 | 2015-04-13 10:42:11 | T1Row1Event1 | 3 | 1 | 2015-04-13 10:42:12 | T1Row1Event3 | | 1 | T1Row1 | 2 | 1 | 2015-04-13 10:42:12 | T1Row1Event2 | 3 | 1 | 2015-04-13 10:42:12 | T1Row1Event3 | | 1 | T1Row1 | 3 | 1 | 2015-04-13 10:42:12 | T1Row1Event3 | NULL | NULL | NULL | NULL | +----+--------+----+------+---------------------+--------------+------+------+---------------------+--------------+ 4 rows in set (0.00 sec) mysql> SELECT -> t1a.*, -> t2a.*, -> t2b.K2 as K2B, -> t2b.K1r as K1rB, -> t2b.rowTimestamp as rowTimestampB, -> t2b.Event as EventB -> FROM -> T1 as t1a -> JOIN T2 as t2a -> ON t2a.K1r = t1a.K1 -> LEFT JOIN ( -> SELECT -> t2i.* -> FROM -> T1 as t1i -> LEFT JOIN T2 as t2i -> ON t2i.K1r = t1i.K1 -> WHERE -> t1i.K1 = 1 -> and t2i.K2 IS NOT NULL -> ) as t2b -> ON t2b.K1r = t1a.K1 -> AND t2b.rowTimestamp > t2a.rowTimestamp -> OR (t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2) -> WHERE -> t1a.K1 = 1 -> and t2b.K2 IS NULL -> ; +----+--------+----+------+---------------------+--------------+------+------+---------------+--------+ | K1 | Name | K2 | K1r | rowTimestamp | Event | K2B | K1rB | rowTimestampB | EventB | +----+--------+----+------+---------------------+--------------+------+------+---------------+--------+ | 1 | T1Row1 | 1 | 1 | 2015-04-13 10:42:11 | T1Row1Event1 | NULL | NULL | NULL | NULL | | 1 | T1Row1 | 2 | 1 | 2015-04-13 10:42:12 | T1Row1Event2 | NULL | NULL | NULL | NULL | | 1 | T1Row1 | 3 | 1 | 2015-04-13 10:42:12 | T1Row1Event3 | NULL | NULL | NULL | NULL | +----+--------+----+------+---------------------+--------------+------+------+---------------+--------+ 3 rows in set (0.00 sec) mysql> SELECT -> t1a.*, -> t2a.*, -> t2b.K2 as K2B, -> t2b.K1r as K1rB, -> t2b.rowTimestamp as rowTimestampB, -> t2b.Event as EventB -> FROM -> T1 as t1a -> JOIN T2 as t2a -> ON t2a.K1r = t1a.K1 -> LEFT JOIN ( -> SELECT -> t2i.* -> FROM -> T1 as t1i -> JOIN T2 as t2i -> ON t2i.K1r = t1i.K1 -> WHERE -> t1i.K1 = 1 -> ) as t2b -> ON t2b.K1r = t1a.K1 -> AND t2b.rowTimestamp > t2a.rowTimestamp -> OR (t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2) -> WHERE -> t1a.K1 = 1 -> -- and t2b.K2 IS NULL -> ; +----+--------+----+------+---------------------+--------------+------+------+---------------------+--------------+ | K1 | Name | K2 | K1r | rowTimestamp | Event | K2B | K1rB | rowTimestampB | EventB | +----+--------+----+------+---------------------+--------------+------+------+---------------------+--------------+ | 1 | T1Row1 | 1 | 1 | 2015-04-13 10:42:11 | T1Row1Event1 | 2 | 1 | 2015-04-13 10:42:12 | T1Row1Event2 | | 1 | T1Row1 | 1 | 1 | 2015-04-13 10:42:11 | T1Row1Event1 | 3 | 1 | 2015-04-13 10:42:12 | T1Row1Event3 | | 1 | T1Row1 | 2 | 1 | 2015-04-13 10:42:12 | T1Row1Event2 | 3 | 1 | 2015-04-13 10:42:12 | T1Row1Event3 | | 1 | T1Row1 | 3 | 1 | 2015-04-13 10:42:12 | T1Row1Event3 | NULL | NULL | NULL | NULL | +----+--------+----+------+---------------------+--------------+------+------+---------------------+--------------+ 4 rows in set (0.00 sec) mysql> SELECT -> t1a.*, -> t2a.*, -> t2b.K2 as K2B, -> t2b.K1r as K1rB, -> t2b.rowTimestamp as rowTimestampB, -> t2b.Event as EventB -> FROM -> T1 as t1a -> JOIN T2 as t2a -> ON t2a.K1r = t1a.K1 -> LEFT JOIN ( -> SELECT -> t2i.* -> FROM -> T1 as t1i -> JOIN T2 as t2i -> ON t2i.K1r = t1i.K1 -> WHERE -> t1i.K1 = 1 -> ) as t2b -> ON t2b.K1r = t1a.K1 -> AND t2b.rowTimestamp > t2a.rowTimestamp -> OR (t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2) -> WHERE -> t1a.K1 = 1 -> and t2b.K2 IS NULL -> ; +----+--------+----+------+---------------------+--------------+------+------+---------------+--------+ | K1 | Name | K2 | K1r | rowTimestamp | Event | K2B | K1rB | rowTimestampB | EventB | +----+--------+----+------+---------------------+--------------+------+------+---------------+--------+ | 1 | T1Row1 | 3 | 1 | 2015-04-13 10:42:12 | T1Row1Event3 | NULL | NULL | NULL | NULL | +----+--------+----+------+---------------------+--------------+------+------+---------------+--------+ 1 row in set (0.00 sec) mysql>