Imagine I've got a sport where I've got a table, MATCH, and a table PLAYER, and the table MATCH_PLAYER. The MATCH_PLAYER table obviously links a certain match to certain players, and in the table MATCH_PLAYER I store information such as the position played, number of points scored etc. by each player. The MATCH table contains which team won, what date it was played etc.
I can make queries such as "what is the % win rate when player_id plays" using this, when you supply the player ID:
SELECT
sum(case when m.win_left = mp.left then 1 else 0 end) * 100.00
/ count(*) as win_percentage
FROM
match m
inner join match_player mp
on mp.match_id = m.id
WHERE mp.player_id = [whatever]
However, what I don't know is how to say "what is the % win rate when player_id x and player_id y play together".
This is obviously a fundamental idea behind databases but I can't for the life of me figure out the terminology for what I want to do, and so searching for it has been hard.
Simply join to the table twice - once for each player:
SELECT
SUM(CASE WHEN (m.win_left = mp.left) AND (M.win_dire = MP2.left) THEN 1 ELSE 0 END) * 100.00
/ COUNT(*) AS win_percentage
FROM
Match M
INNER JOIN Match_Player MP1 ON
MP1.match_id = M.id AND
MP1.player_id = [whatever]
INNER JOIN Match_Player MP2 ON
MP2.match_id = M.id AND
MP2.player_id = [whatever #2]
If you're going to go beyond two or three players then there are some other approaches through set matching that might be more efficient as the number of players gets larger, but for your case this will likely be fastest.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments