How to get the matching db record
There are 3 DB tables involved.
P1 P2 P3 P4 P5 .....
T1 T2 T3 T4 T5 ....
T1 P1 T1 P2 T1 P3 T1 P4 T2 P1 T2 P2 T2 P3 ......
take into account the multiple combinations of no. of players. Also, a player can be in multiple teams
Ex. (P1 P2 P3 P4), (P1 P2 P3), (P1 P2), (P1 P3), (P2 P3) .. so on
In my app, I get a set of players playing in a particular match as a team. Given this set, i want to find the common team they belong to. Even if they are in multiple teams, the team containing only this set of players will be unique. So in above example, there is only one team which has players P1 P2 P3 and P4. But if the team has only 3 players P1 P2 P3, then we have more than one common team. Because, we might have combinations T1(P1 P2 P3 P4) T2(P1 P2 P3). Of course, we should not count T1 since it has P4, but exactly how should we do that?
So can you suggest a way (SQL query or program logic) to do this easy and efficiently? Is there any sql function to get common team name (i.e. T2) ?
Any help is greatly appreciated ?
You should use this query where 4 in this example is a count of players in set (P1,P2,P3,P4). If all Players in this team then for this TeamId count(PlayerID) = count of all players in the set:
select teamId from Player_Team where PlayerId in (P1_Id,P2_ID,P3_ID,P4_ID) group by teamId having count(PlayerId) = 4