0

I've hit a somewhat unique obstacle with my current project. Below is a mock MySQL table to better describe the issue I'm trying to solve.

mysql> describe `minimized_event_instructions`;
+-----------------+------------------+
| Field           | Type             |
+-----------------+------------------+
| id              | int(10) unsigned |
| event_inst_min  | tinytext         |
| custom_time_1   | datetime         |
| custom_time_2   | datetime         |
| custom_time_3   | datetime         |
| deploy_time     | datetime         |
| completion_time | datetime         |
| error           | tinytext         |
+-----------------+------------------+

Of particular note are the custom_time_X fields. Each of the 3 fields provides a date and time for a particular stage of each event. However, I've come across the need to order the table by these fields collectively.

I tried the following query - thought I was being clever:

mysql> SELECT  `a`.`custom_time_1` AS `combined_times`,
    ->         `b`.`custom_time_2` AS `combined_times`,
    ->         `c`.`custom_time_3` AS `combined_times`
    -> 
    -> FROM `minimized_event_instructions` AS `a`
    -> CROSS JOIN `minimized_event_instructions` AS `b`
    -> CROSS JOIN `minimized_event_instructions` AS `c`;

... but I ended up getting this.

+---------------------+---------------------+---------------------+
| combined_times      | combined_times      | combined_times      |
+---------------------+---------------------+---------------------+
| 2019.10.23 01:15:00 | 2019.10.23 05:25:00 | 2019.10.23 10:30:00 |
| 2019.10.23 01:15:00 | 2019.10.23 05:25:00 | 2019.10.23 10:30:00 |
| 2019.10.23 01:15:00 | 2019.10.23 05:25:00 | 2019.10.23 10:30:00 |
+---------------------+---------------------+---------------------+

Then I tried using UNION:

SELECT  `a`.`custom_time_1` AS `combined_times`
FROM `client_custom_sms_times` AS `a`
UNION
SELECT  `b`.`custom_time_2` AS `combined_times`
FROM `client_custom_sms_times` AS `b`
UNION
SELECT  `c`.`custom_time_3` AS `combined_times`
FROM `client_custom_sms_times` AS `c`;

But that just gave me this ... which is soooo close - but totally unusable:

+---------------------+
| combined_times      |
+---------------------+
| 2019.10.23 01:15:00 |
| 2019.10.23 05:25:00 |
| 2019.10.23 10:30:00 |
+---------------------+

I've tried a few other things to absolutely no avail. The result set I'm looking for would ideally be something more like this:

+----+----------------+---------------------+-------------+-----------------+-------+
| id | event_inst_min | combined_times      | deploy_time | completion_time | error |
+----+----------------+---------------------+-------------+-----------------+-------+
|  5 | inst:f2:rak... | 2019.10.23 01:15:00 |        null |            null |  null |
| 12 | inst:f2:211... | 2019.10.23 01:20:00 |        null |            null |  null |
|  2 | inst:71        | 2019.10.23 02:30:00 |        null |            null |  null |
| 12 | inst:f2:211... | 2019.10.23 02:35:00 |        null |            null |  null |
|  2 | inst:71        | 2019.10.23 03:00:00 |        null |            null |  null |
|  5 | inst:f2:rak... | 2019.10.23 05:25:00 |        null |            null |  null |
| 12 | inst:f2:211... | 2019.10.23 10:30:00 |        null |            null |  null |
|  2 | inst:71        | 2019.10.23 10:05:00 |        null |            null |  null |
+----+----------------+---------------------+-------------+-----------------+-------+

I've wasted way too much time trying to solve this problem ... any thoughts? I had considered making a temporary table, and just sticking 3 records in for every one record from the original table; but if there's a more - direct method using a single query, that would be ideal.

Thanks in advance for any help anybody might have.

  • granted, I'll probably have to drop the id column so I don't get any issues trying to put duplicate keys in there - but that seems like a minor issue to overcome. The main problem is getting the column to merge in this particular manner. – CreationTribe Oct 16 at 18:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.