user3567212 user3567212 - 3 months ago 17
MySQL Question

MySQL - GROUP BY with MIN and MAX - Fall within date range of solution

I have the following tables with data:

Table1:

id name race_type start_time end_time
--- ---- --------- ---------- --------
111 Phelps Relay 2016-08-20 00:01:00 NULL
111 Phelps Relay NULL 2016-08-20 00:02:00
222 Phelps Relay 2016-08-20 00:03:00 NULL
222 Phelps Relay NULL 2016-08-20 00:04:00
333 Lochte Butterfly 2016-08-20 00:05:00 NULL
333 Lochte Butterfly NULL 2016-08-20 00:06:00
444 Lochte Butterfly 2016-08-20 00:07:00 NULL
444 Lochte Butterfly NULL 2016-08-20 00:08:00


Table2:

name race_type current_time qualifies
---- --------- ------------ ---------
Phelps Relay 2016-08-20 00:03:30
Lochte Butterfly 2016-08-20 00:05:30


For the two transaction in Table2, I need to determine if the CURRENT_TIME for those transactions falls within the START_TIME and END_TIME of Table1 records with respect to each unique ID pairing, name, and race_type.

What I was thinking was to first "merge" the data (id) in Table1 using GROUP BY with MIN and MAX functions:

SELECT id,name,race_type, MIN(start_time) AS start_time, MAX(end_time) AS end_time
FROM Table1
GROUP BY id


This would give me the following results:

+-----+--------+---------------+----------------------+---------------------+
| id | name | race_type | start_time | end_time |
+-----+--------+---------------+----------------------+---------------------|
| 111 | phelps | relay | 2016-08-20 00:01:00 | 2016-08-20 00:02:00 |
| 222 | phelps | relay | 2016-08-20 00:03:00 | 2016-08-20 00:04:00 |
| 333 | lochte | Butterfly | 2016-08-20 00:05:00 | 2016-08-20 00:06:00 |
| 444 | lochte | Butterfly | 2016-08-20 00:06:00 | 2016-08-20 00:08:00 |
+-----+--------+---------------+----------------------+---------------------+


Based on these results I could more easily determine if phelps or lochte current_time for their relay or butterfly swim (in Table2) was in either of the start or end time ranges for their respective name and race_type. If it does fall within one of those ranges, I would set the Table2 qualifies parameter to true.

Could someone recommend a MySQL query that could accomplish this task? I'm guessing I could use some combination of GROUP BY to first "merge" the id's in Table 1, than use
exists
?

Greatly appreciate any advice and thank you in advance!

Answer

you can use inner join in order to get all results that the current time falls between the range:

SELECT * FROM (
SELECT id,name,race_type, MIN(start_time) AS start_time, MAX(end_time) AS end_time
FROM Table1
GROUP BY id
) AS results INNER JOIN table2 ON 
  results.name = Table2.name 
  AND results.type = Table2.type 
  AND Table2.current_time BETWEEN results.start_time AND results.end_time;
Comments