2

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

2

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.

1
  • Well, I have moved condition to ON clause and replaced count(*) with COUNT(m.MATCH_ID) and it works. Thank you. :) May 2 '15 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.