Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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...

share|improve this question
    
Maybe try it with minutes_played IS NULL –  Mihai Jan 3 at 14:06
    
@Mihai doesn't work either... –  Syl Jan 3 at 14:08
    
Any way you could set up a table and test data on sqlfiddle.com? I'd love to play around with it. –  Joe Love Jan 3 at 15:03
    
when debugging problems like this, try moving the case statement out of the where clause and into the select list as a returned boolean field. the problem is probably in how your filtering the data –  user32365 Jan 3 at 15:28
    
@JoeLove I tried but I keep getting a "Your schema ddl is too large (more than 8000 characters). Please submit a smaller DDL." error even though I'm trying to use the minimum amount of data to test it with just player_fixtures :/ –  Syl Jan 3 at 16:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.