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].
>Solution :
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;