How to execute different query based on returned value?
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
- https://www.db-fiddle.com/f/2f7NEPo72tUM7zLHBX2GXQ/0
- https://www.db-fiddle.com/f/3ghG6zf7hv2SbACL9vtnJa/1
- https://www.db-fiddle.com/f/q7DgtJRfDxyPA8bQheRncA/1
- https://www.db-fiddle.com/f/tV7VhZg1Ywfx1YmnFMtZdh/1