remedy remedy - 2 months ago 13
MySQL Question

How to set a limit on the relationship between data in SQL

So, I have a Player table which has fields like PlayerId, firstname, lastname etc.. and a Teams table with fields such as, TeamId, teamname, etc.. I also have a look up table linking the two tables together by showing which player is on which team. My question is, if I wanted to display to the user the Teams that have not exceeded the 20 Player limit and still have room on the team, how would I go about doing that?

Answer

Show teams with quantity of players not exceeding 20:

SELECT t.teamname
FROM team t
LEFT JOIN player_team pt ON
  t.teamid = pt.teamid
GROUP BY t.teamname
HAVING COUNT(pt.playerid) <= 20 -- you probably mean < 20

Add information on slots availability:

SELECT t.teamname, CONCAT(COUNT(pt.playerid), '/20') AS slots_taken -- if 20 is max
FROM team t
LEFT JOIN player_team pt ON
  t.teamid = pt.teamid
GROUP BY t.teamname
HAVING COUNT(pt.playerid) <= 20 -- you probably mean < 20
Comments