How to join two query that compare different results?

Question

I have a table called match that contains all the matches of a soccer competition. The structure of the table is the following:

id | round_id | datetime | status | home | away | result

essentially each match have a status that indicate:

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

there is also the round_id that indicate the reference of the competition.

In my application I check each time if a specific round need to be updated. For doing so, first I check if all the matches of the league contains a status of 3 or 5. The query is the following:

SELECT (COUNT(*) = SUM(status in (3, 5)) 
                   AND COUNT(*) = 380) AS result 
                   FROM `match` WHERE round_id = 10

So if the 380 matches are cancelled or are finished, then the round 10 is updated.

If the result of the query above is false, so the round need to be updated, then I have to check which matches need to be updated.

For doing that I wrote the following query:

SELECT COUNT(*) AS result FROM `match` m2 
                   WHERE m2.round_id = 10
                   AND m2.datetime < NOW() AND m2.status IN (1, 2)

the query above check if there are matches with a datetime lower than the current one, and if the status of them is 1 or 2, specifically (scheduled and postponed).

I'm not really satisfied about my implementation for the following reasons:

1. I need to execute two query for check if I need to update.
2. The last query return the number of the match, but my goal is return a boolean. Unfortunately my skills of sql is not so advanced.

Is possible join this two query in one to simply the checking?

Thanks in advance for the help.

UPDATE - DATABASE WITH DATA STRUCTURE:

id | round_id |        datetime      | status | home | away | result
 1      10      2018-08-15 00:00:00      5        A      B     1-0
 2      10      2018-08-15 00:00:00      5        C      D     1-1
 3      10      2018-08-15 00:00:00      5        E      F     2-1
 4      10      2018-08-15 00:00:00      5        G      H     2-2

the matches above are all updated 'cause the status is 5 so the round 10 doesn't need any update. The first query will return true and the second query will return 0.

Consider now this example:

id | round_id |        datetime      | status | home | away | result
 1      11      2018-08-15 00:00:00      5        A      B     1-0
 2      11      2019-08-15 00:00:00      1        C      D      
 3      11      2019-01-15 00:00:00      5        E      F     2-1
 4      11      2019-03-12 00:00:00      1        G      H      

the round 11 need to be updated because we have 2 matches that are scheduled, but only the match with the id 4 has already played, so the query need to return false because the round 11 isn't updated.

Answer 1

I think you want something like this:

SELECT (COUNT(*) = SUM(status in (3, 5)) AND
        COUNT(*) = 380 AND
        SUM(mdatetime < NOW() AND status IN (1, 2)) = 0
       ) AS result 
FROM `match`
WHERE round_id = 10;

I think you just want to check if the second query returns a count greater than 0. In any case, you can combine all these conditions into a single boolean result.