The error is simple - INNER JOIN will only select complete pairs.

You must collect all key values in a separate subquery and then join the data into it.

SELECT t1.start_date_time,t2.start_date_time as end_date_time,t1.number1,t1.number2 FROM ( SELECT key1 AS key FROM table UNION -- combine columns values and remove duplicates SELECT key2 AS key FROM table ) all_keys LEFT JOIN table t1 ON all_keys.key = t1.key1 LEFT JOIN table t2 ON all_keys.key = t2.key2

If thekey1value always exists, while the same value inkey2may not exist, then you can use a simple LEFT JOIN:

SELECT t1.start_date_time,t2.start_date_time as end_date_time,t1.number1,t1.number2 FROM table t1 LEFT JOIN table t2 ON t1.key1 = t2.key2