How to execute different query based on returned value?

Question

I have a list of football matches defined as follow:

id |        datetime      | status | gameweek | round_id | home_team_id
 1   2019-03-31 00:00:00      1          29     12696          1243
 2   2019-03-31 00:00:00      1          29     12696          1248
 3   2019-03-31 00:00:00      1          29     12696          1242
 4   2019-03-31 00:00:00      1          29     12696          1246
 5   2019-03-31 00:00:00      1          29     12696          1244
 6   2019-03-31 00:00:00      1          29     12696          1247
 7   2019-03-31 20:30:00      1          29     12696          1241
 8   2019-03-31 00:00:00      1          29     12696          1249
 9   2019-03-31 00:00:00      1          29     12696          2981
 10  2019-03-31 00:00:00      1          29     12696          1259

I need to return all the matches which have as gameweek the next gameweek to the finished matches not all.

Some rounds doesn't have any gameweek, so in that case should be returned all the matches which have a datetime next to the finished match.

The query I wrote is this:

Select m.* from `match` m where round_id = 12696 and m.datetime = (SELECT COALESCE(MIN(CASE WHEN m2.status < 5 THEN m2.datetime END), MAX(m2.datetime)) FROM `match` m2 WHERE m2.round_id = m.round_id)

this return only 9 records, and I don't understand why, the only reason is that one record have the time too.

What does finished matches mean?

For matches ended or finished I mean that the status of each match is 5 or 3. A status of 1 means that the match is scheduled, but hasn't played yet; 5 means finished and 3 canceled.

eg:

id |        datetime      | status | gameweek | round_id | home_team_id
 1   2019-03-20 00:00:00      5          29     12696          1243
 2   2019-03-20 00:00:00      5          29     12696          1248
 3   2019-03-20 00:00:00      5          29     12696          1242
 4   2019-03-31 00:00:00      1          29     12696          1246
 5   2019-03-31 00:00:00      1          29     12696          1244
 6   2019-03-31 00:00:00      1          29     12696          1247
 7   2019-03-31 20:30:00      1          29     12696          1241
 8   2019-03-31 00:00:00      1          29     12696          1249
 9   2019-03-31 00:00:00      1          29     12696          2981
 10  2019-03-31 00:00:00      1          29     12696          1259

as you can see the first three records are already played. In that case the query need to return all the matches (played and scheduled) because the gameweek 29 contains also other matches that hasn't played yet, so the expected result are all the 10 records.

expected result: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

Another important thing is that some round doesn't have any gameweek, so supposing this, we need to return the upcoming matches, eg:

id |        datetime      | status | gameweek | round_id | home_team_id
 1   2019-03-20 00:00:00      5        NULL     12696          1243
 2   2019-03-20 00:00:00      5        NULL     12696          1248
 3   2019-03-20 00:00:00      5        NULL     12696          1242
 4   2019-03-31 00:00:00      1        NULL     12696          1246
 5   2019-03-31 00:00:00      1        NULL     12696          1244
 6   2019-03-31 00:00:00      1        NULL     12696          1247
 7   2019-03-31 20:30:00      1        NULL     12696          1241
 8   2019-03-31 00:00:00      1        NULL     12696          1249
 9   2019-03-31 00:00:00      1        NULL     12696          2981
 10  2019-03-31 00:00:00      1        NULL     12696          1259

expected result: 4, 5, 6, 7, 8, 9, 10

(In the fiddle the record 7 is missing).

If there are no gameweeks, but all the matches are finished (status 5), then we need to return all the matches of the latest datetime, eg:

id |        datetime      | status | gameweek | round_id | home_team_id
 1   2019-03-20 00:00:00      5        NULL     12696          1243
 2   2019-03-20 00:00:00      5        NULL     12696          1248
 3   2019-03-20 00:00:00      5        NULL     12696          1242
 4   2019-03-31 00:00:00      5        NULL     12696          1246
 5   2019-03-31 00:00:00      5        NULL     12696          1244
 6   2019-03-31 00:00:00      5        NULL     12696          1247
 7   2019-03-31 20:30:00      5        NULL     12696          1241
 8   2019-03-31 00:00:00      5        NULL     12696          1249
 9   2019-04-05 00:00:00      5        NULL     12696          2981
 10  2019-04-05 00:00:00      5        NULL     12696          1259

expected result: 9, 10

id |        datetime      | status | gameweek | round_id | home_team_id
 1   2019-03-20 00:00:00      5        28     12696          1243
 2   2019-03-20 00:00:00      5        28     12696          1248
 3   2019-03-20 00:00:00      1        28     12696          1242
 4   2019-03-31 00:00:00      1        28     12696          1246
 5   2019-04-05 00:00:00      5        29     12696          1244
 6   2019-04-05 00:00:00      5        29     12696          1247
 7   2019-04-05 20:30:00      5        29     12696          1241
 8   2019-04-05 00:00:00      5        29     12696          1249

Expected result: 1,2,3,4,

I created a fiddle here which covers all the cases.

Answer 1

This feels like an unnecessarily complex query, but it does match the outputs stated above. Might be a good starting point.

with current_round as (
    select * 
    from match_case_1
    where round_id = 12696
)
select *
from current_round cr
where
    (
        not exists(select * from current_round where gameweek is null)
    )
    or 
    (
        exists(select * from current_round where status = 1) 
        and not exists(select * from current_round where gameweek is not null)
        and cr.status = 1    
    )
    or 
    (
        not exists(select * from current_round where status = 1)
        and not exists(select * from current_round where gameweek is not null)
        and cast(cr.`datetime` as date) = (
            select max(cast(`datetime` as date)) as `date`
            from current_round
            where status = 5 or status = 3
        )
    );

EDIT

DB Fiddle Queries on scenarios posted

1. https://www.db-fiddle.com/f/2f7NEPo72tUM7zLHBX2GXQ/0
2. https://www.db-fiddle.com/f/3ghG6zf7hv2SbACL9vtnJa/1
3. https://www.db-fiddle.com/f/q7DgtJRfDxyPA8bQheRncA/1
4. https://www.db-fiddle.com/f/tV7VhZg1Ywfx1YmnFMtZdh/1

Answer 2

Given that it doesn't look like your matches will be changing super often, my advice would be to save yourself the headache and split up these queries in your application layer (if one exists)

Going from your examples, you can ask your DB a series of questions to get the results you want

Is there a lowest gameweek that has unfinished matches for round 12696?

SELECT MIN(gameweek) min_gameweek 
  FROM `match` 
 WHERE round_id = 12696
   AND gameweek IS NOT NULL
   AND status = 1

If yes.. What are the matches for that gameweek?

SELECT *
  FROM `match` 
 WHERE round_id = 12696
   AND gameweek = :min_gameweek

If no.. (you may wish to try to find the maximum gameweek for finished matches in round 12696 here, and list all matches from that)

If still no.. What are the unfinished matches without a gameweek in round 12696?

SELECT *
  FROM `match`
 WHERE round_id = 12696
   AND gameweek IS NULL
   AND status = 1

If none.. Is their a latest datetime for a finished match without a gameweek in round 12696?

SELECT MAX(`datetime`) latest_datetime
  FROM `match`
 WHERE round_id = 12696
   AND gameweek IS NULL
   AND status != 1

If yes.. What are the finished matches without a gameweek in round 12696 that have this latest datetime?

SELECT *
  FROM `match` 
 WHERE round_id = 12696
   AND `datetime` = :latest_datetime

N.B. This approach involves more queries, but is vastly more readable, debuggable, and flexible, and may even perform better than an attempt at one monster query

You could probably combine some of the queries above with adding too much opacity, but I'd still start by writing each step out and testing it

I would also seriously suggest simplifying the whole process as follows

• Store the current gameweek and max gameweek for each round in the round table (NULLed if there are no gameweeks for that round)
• Simply show all the matches for the current gameweek for the round in question
• When a match finishes (or at the end of the day/week), increment the current gameweek if all matches are finished and max is not yet reached