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