user3567212 user3567212 - 1 year ago 61
MySQL Question

MySQL merge rows with same ID while preserving timestamps

I have the following table with data:

Table 1:

-- ---------- --------
1 2016-08-20 00:01:00 NULL
1 2016-08-20 00:02:00 NULL
1 NULL 2016-08-20 00:03:00
1 NULL 2016-08-20 00:04:00

I would like to "merge" the data for the unique ID 1 so that I can easily read it. It should look like the following (basically grabbing the earliest timestamp for START_DATE and latest timestamp for END_DATE):

Table 2:

-- ---------- --------
1 2016-08-20 00:01:00 2016-08-20 00:04:00

This is ultimately so that I can determine if the following TX_ID record falls within the START_DATE and END_DATE and set the FALL_WITHIN_START_AND_END parameter to True.

Table 3:

----- --------- -------------------------
123456 2016-08-20 00:02:35 True

Could someone recommend a MySQL query to "merge" Table 1 value so that it would look like Table 2? This is so I can more easily determine if the TX_ID in Table 3 falls within the complete start and end date range.

Thank you in advance for your help!

Answer Source

Barmar has a fine approach for the first part of your question. But, for the full answer, you can use exists:

select t3.*,
       (case when exists (select 1
                          from table1 t1
                          where = and t1.start_date <= t3.timestamp
                         ) and
                  exists (select 1
                          from table1 t1
                          where = and t1.end_date >= t3.timestamp
             then 1 else 0
        end) as fall_within_start_and_end 
from table3 t3;

The advantage of this approach over the group by is that it can take advantage of indexes on table1(id, start_date) and table1(id, end_date).