I'm creating a SQL query with a some nested queries and I'm trying to use the CASE statement but it is behaving weirdly. I've been at it for at least 6 hours without any luck...

This is my query at the moment:

select t.fpl_id, t.team_name, sum(pf.points)as gwpts, ( select sum(transfers_malus) from gameweeks where gameweeks.team_id = t.id and gameweeks.number = g.number )as malus, ( select sum(points) from player_fixtures as pfix where gw_number = g.number and pfix.player_id = CASE WHEN minutes_played > 0 THEN g.captain_id ELSE g.vice_captain_id END ) as cpts, ( select max(web_name) from players join player_fixtures on players.id = player_fixtures.player_id and gw_number = g.number where players.id = CASE WHEN player_fixtures.minutes_played > 0 THEN g.captain_id ELSE g.vice_captain_id END ) as captain_name, array_agg(p.id) as lineup from teams as t join gameweeks as g on t.id = g.team_id join gameweeks_players as gp on gp.gameweek_id = g.id join players as p on gp.player_id = p.id join player_fixtures as pf on p.id = pf.player_id and pf.gw_number = g.number where t.id = 1 group by t.id, g.id order by g.number asc

he bit where I have a problem is when I'm doing the case statement to see if one of the player ( the captain ) hasn't played based on "minutes_played":

(select sum(points) from player_fixtures as pfix where gw_number = g.number and pfix.player_id = CASE WHEN pfix.minutes_played > 0 THEN g.captain_id ELSE g.vice_captain_id END ) as cpts

and

( select max(web_name) from players join player_fixtures on players.id = player_fixtures.player_id and gw_number = g.number where players.id = CASE WHEN player_fixtures.minutes_played > 0 THEN g.captain_id ELSE g.vice_captain_id END ) as captain_name,

My expected result would be that if minutes_played is greater than 0 then return the row that matches gw_number = g.number and player_id = g.captain_id, otherwise return the row that matches gw_number = g.number and player_id = g.vice_captain_id

When minutes_played is indeed greater than 0, everything works fine. However, when it is equal to 0 it doesn't return any row and thus I get a NULL in the row of my overall query.

The funny thing is that if I try with the following script on the player_fixtures where minutes_played = 0 my greater than evaluation works and I get the correct "BAD".

DO LANGUAGE plpgsql $$ BEGIN IF (select minutes_played from player_fixtures where gw_number = 19 and player_id = 266 ) > 0 THEN RAISE NOTICE 'GOOD'; ELSE RAISE NOTICE 'BAD'; END IF; END; $$;

I'm pretty new at this whole DB thing so I'm probably making a rookie mistake but after trying to solve it myself for 6hours, I think it is time I ask for help. Could someone point me in the right direction?

Thanks!

EDIT with more info following @Mihai answer:

when I cover the 0 condition it still return 0 row. I tried with the following test data:

select * from player_fixtures where gw_number = 19 and player_id = case when player_fixtures.minutes_played > 0 then 266 when player_fixtures.minutes_played = 0 then 214 end

If I run

select * from player_fixtures where gw_number = 19 and player_id in (266,214)

I get 2 rows with the correct data so there must be a problem when I do the case statement...