Query with group by having count 0

group byjoin;oracle

I am working on application for managing football league as my homework. I want filter list of players by number of matches they have played in league. For example user can enter count > 0 and count < 5.

I wrote query which works fine when count is bigger than zero. However, in some cases user may want include players that weren't in any matches, yet. For example:

SELECT p.PLAYER_ID, p.NAME, p.SURNAME, p.POSITION, p.TEAM_ID, p.DRESS_NUM FROM player p 
 LEFT JOIN player_in_squad psq ON p.PLAYER_ID = psq.PLAYER_ID 
 LEFT JOIN squad sq ON sq.SQUAD_ID = psq.SQUAD_ID
 LEFT JOIN squad_match sqm ON sqm.SQUAD_ID = psq.SQUAD_ID 
 LEFT JOIN match m ON m.MATCH_ID = sqm.MATCH_ID
     WHERE sqm.HOME = 'N' 
     AND m.LEAGUE_YEAR = 1
     AND m.LEAGUE_TYPE_ID = 301
    GROUP BY p.PLAYER_ID,  p.NAME, p.SURNAME, p.POSITION, p.TEAM_ID, p.DRESS_NUM
    HAVING COUNT(*) < 2;

In this query only count > 0 is returned. So I have created manual condition which is detecting if user wants include count = 0 in his filtered list and then I add this to beginning of the query: 

SELECT p.PLAYER_ID, p.NAME, p.SURNAME, p.POSITION, p.TEAM_ID, p.DRESS_NUM FROM player p INTERSECT

… but I don't like this workaround solution and I suppose neither my teacher will.

I think there must be better approach. Any ideas? Thy

Best Answer

Having clause can only exclude rows which exist in data - in your case only possibility to get count(*)=0 means having no matching rows - so nothing to return in the first place.

You probably want to count existing matches in the left join - so COUNT(m.MATCH_ID) will only count rows where match_id is not null.

Edit: One more thing I noticed - you have lot of conditions in WHERE which touch left-joined tables - this will actually cancell the effect of left join, because ie "m.LEAGUE_YEAR = 1" is false when no matchng row exists and so league_year is null - you want to move those conditions to ON as "join-cond AND additionalCont1 AND ..." for each table (m and sqm in your case) - then it will get the chance to actually give you some of those players without a match.

Related Question