CODEWITHSUNDEEP
mysql
Sebastian Salines
Sebastian Salines

Reputation: 81

Get loser of match where it only shows winner

I have a table called teams (id, name), matches (id, team1, team2), results (match, winner)

How could I select the loser of match x based on the tables matches and results? Is it possible to do with MySQL or do I have to do it on PHP?

EDIT: matches - team1 and team2 reference teams(id) results - match references matches(id) and winner references teams(id)

I need to get the id of the losing team.

Upvotes: 1

Views: 89

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I would do this with a join and case statement:

select m.id,
       (case when r.winner = m.team1 then team2
             else team1
        end) as loser
from matches m join
     results r
     on m.id = r.match;

Because you only want the id, the teams table is not needed.

Upvotes: 2

Mureinik
Mureinik

Reputation: 311163

Assuming that you do not allow draws, the loser is the team that participated in the match and is not the winner, so (using X as the match ID):

SELECT name
FROM   teams
WHERE  id IN (SELECT team 
              FROM   (SELECT match, team1 AS team
                      FROM   matches
                      WHERE  id = X
                      UNION ALL
                      SELECT match, team1 AS team
                      FROM   matches
                      WHERE  id = X) t
              JOIN   results
              ON     t.match = winner.match AND t.team != results.winner)

Upvotes: 2

Related Questions