Check if combination of items already exists in many-to-many MySQL tables-CodePudding

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].

CodePudding user response：

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;