COALESCE not return all result

Question

I want return all the matches available for a list of rounds, the round is something like a group that organize a list of match.

I want return from each rounds the matches of the next gameweek, a gameweek is when a match is played eg. (1, 2, 3, 4 etc...) and is played into a specific date.

A match can have 5 status, this tell me if the gameweek was played or not:

1. Scheduled
2. Cancelled
3. Playing
4. Postponed
5. Finished

Problem

I have 4 rounds which have different matches, each of those rounds is finished, so the query should return all the matches for all the rounds with the max gameweek, the problem's that I get only 2 rounds, the other 2 missing.

My query is this:

SELECT m.id, m.round_id, m.datetime,
      CASE m.status
        WHEN 1 THEN 'scheduled'
        WHEN 2 THEN 'postponed'
        WHEN 3 THEN 'canceled'
        WHEN 4 THEN 'playing'
        WHEN 5 THEN 'finished'
      END AS match_status,
      c.name AS competition_name,
      c.id AS competition_id,
      r.name AS round_name
      FROM `match` m
      LEFT JOIN competition_rounds r ON m.round_id = r.id
      LEFT JOIN competition_seasons s ON r.season_id = s.id
      LEFT JOIN competition c ON c.id = s.competition_id
      WHERE 1 AND m.round_id IN (52, 53, 54, 55) AND m.gameweek = 
                  (SELECT COALESCE(MIN(CASE WHEN m2.status < 5 THEN m2.gameweek END), MAX(m2.gameweek))
                  FROM `match` m2 
                  WHERE 1 AND m2.round_id IN (52, 53, 54, 55) )

Data Example

competition_seasons (organize a competition by season)

 | id | name | competition_id
    1    2018    22

competition_rounds (contains all the rounds available)

 | id | name | season_id
   52   Foo1     1
   53   Foo2     1
   54   Foo3     1 
   55   Foo4     1

match:

 |   id  | status | round_id | gameweek
     1      5          52       10
     2      5          52       9
     3      5          52       8
     4      5          53       5
     5      5          53       5
     6      5          53       2
     7      5          54       7
     8      5          55       7
     9      5          55       9
     10     5          55       9

Expected output (id): 1, 4, 5, 7, 9, 10

my query return only 53 and 54, what I did wrong?

Answer 1

You should correlate your subquery with main query, so instead of/after AND m2.round_id IN (52, 53, 54, 55) you should have AND m2.round_id = m.round_id.