I have a MySQL database schema that looks like this:
team teams_players players +--+ +--+-------+---------+ +--+ |id| |id|team_id|player_id| |id| +--+ +--+-------+---------+ +--+ |1 | |1 | 1| a| |a | |2 | |2 | 1| b| |b | +--+ |3 | 1| c| |c | |4 | 1| d| |d | |5 | 1| e| |e | |6 | 2| a| |f | |7 | 2| d| |g | |8 | 2| e| +--+ |9 | 2| f| |10| 2| g| +--+----------+------+
Teams can have many players and a player can be on many teams.
I need to be able to check and see if a particular set of players already exists in the database as a team. If it doesn’t I want to do some INSERTs to create it. If it does exist I just need to get its team_id.
So for instance, given the data above, if I have an array of players, [a,b,c,d,e] I need to look and see that a team already exists made exactly of those players (no more, no less). It does, it’s team_id: 1 in this case.
If I have an array of players such as [a,g,f,b,c] I need to be able to see that such a combination of those players, (no more, no less), does not already exist so I would then be able to add team 3 to the team table and add those players to rows in teams_players and put team 3 as their team_id.
The number of players on a team will always be 5 and their order on the team doesn’t matter. So [a,b,c,d,e] should be seen as the same team as [e,a,b,d,c].
For your first question, you may use the following query to assert that players
[a,b,c,d,e] and no others play on a given team:
SELECT team_id FROM teams_players GROUP BY team_id HAVING COUNT(DISTINCT player_id) = 5 AND SUM(player_id NOT IN ('a', 'b', 'c', 'd', 'e')) = 0;