CODEWITHSUNDEEP
mysql
I love coding
I love coding

Reputation: 1191

SQL query to find the head to head match winning

I have the following MySQL DB tables, which describes the matches and the results in a soccer championchips:

Tables structure and example

These rows come from the following SQL query:

    Select *
From RisultatiFantaAllenatori
Where Giornata = 10

And:

 Select *
From Partite
Where Giornata = 10

I want to calculate the head to head standings between one player and the other ones, for all the championchips. The Matches will be repeated every 9 matchesdays (for example Player1 will play against Player2 on MatchDay #10 and on MatchDay #19). This is something that I'm going to expect:

enter image description here

For get the Win for the Player, the condition is that Player.points > OtherPlayer.points. For example in MatchDay10 if Player1 has obtain 4 points and Player 2 has obtain 1 points, Player1 has 1 head to head win against Player2. I hope to be clear as possibile. So my question is: Is possibile to obtain an SQL query for this scope ? If yes, what is it ? Thank you in advance for your reply.

UPDATE

The attribute name is FantaAllenatore and not FantaAllenatori in the table RisultatiFantaAllenatori. In all case, by this query, is possible to have all the distinct players of the championchips:

    Select Cognome as FantaAllenatore
From FantaAllenatori

Upvotes: 0

Views: 1662

Answers (1)

JNevill
JNevill

Reputation: 50034

I believe something like the following should do the trick:

SELECT
    t1.G1 as Player,
    t1.G2 as OtherPlayer,
    SUM(CASE WHEN t2.Punteggio > t3.Punteggio THEN 1 ELSE 0 END) as `Win for the Player`,
    SUM(CASE WHEN t2.Punteggio < t3.Punteggio THEN 1 ELSE 0 END) as `Win for the OtherPlayer`
FROM
    Partite t1
    LEFT OUTER JOIN RisultatiFantaAllenatori t2 ON
        t1.G1 = t2.FantaAllenatori AND
        t1.Giornata = t2.Giornata
    LEFT OUTER JOIN RisultatiFantaAllenatori t3 ON
        t1.G2 = t3.FantaAllenatori AND
        t1.Giornata = t3.Giornata
GROUP BY t1.G1, t1.G2

The magic here is in the SUM(CASE...) bits where it compares the results of each player match up to determine who one. The other important part is joining in your second table twice so that we get the score for each player for each match.

Upvotes: 1

Related Questions