CODEWITHSUNDEEP
mysqlsqlsqliteinner-join
James
James

Reputation: 373

See if two table rows exist, inner joined to another row

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.

Upvotes: 0

Views: 20

Answers (1)

Tom H
Tom H

Reputation: 47464

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.

Upvotes: 1

Related Questions