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