How to join a table to itself by keys in different columns that can be NULL

Question

I have a data set (example 5 fields) as follows.

start date	time	Number 1	Number 2	Key 1	Key 2
November 21, 2022	0:01	1668969012	413027032418217	B3B820CE
November 21, 2022	0:02				B3B820CE
November 21, 2022	0:03	1668969013	413027032418218	8EFCB769
November 21, 2022	0:04				8EFCB769
November 21, 2022	0:05	1668969014	413027032418219	4070A788

As you can see, the first two lines and the last two lines can be coupled by using the strings in key1 and key2. But for the last field, there is no matching field.

Basically I need to match these pairs and create a new table as shown below.

start date	time	End date	time	Number 1	Number 2
November 21, 2022	0:01	November 21, 2022	0:02	1668969012	413027032418217
November 21, 2022	0:03	November 21, 2022	0:04	1668969013	413027032418218
November 21, 2022	0:05			1668969014	413027032418219

This is what I have tried so far. But this doesn't provide what I need. Can someone tell me what the error is here?

SELECT 
    t1.start_date_time, t2.start_date_time AS end_date_time, t1.number1, t1.number2
FROM details_all AS t1
    JOIN details_all AS t2
      ON t1.key1 = t2.key2

Answer 1

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 the key1 value always exists, while the same value in key2 may 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