Search code examples
mysqlsqldatabaseselectsubquery

How do I use OR in mySQL on a Select statement with a greater-than condition?

So I am basically building this mySQL sports database and have a table in which there is a 'Home_Team' and 'Away_Team', as well as 'Home_Score' and 'Away_Score'. What I need is a query which displays the winning team and score. So it has to select one team if the score is greater than the other team's; and vice-versa. I have tried the following, using an OR statement between the sub-queries:

SELECT *
FROM match
WHERE (SELECT Home_Team FROM match WHERE Home_Score > Away_Score) 
OR (SELECT Away_Team FROM match WHERE Home_Score < Away_Score);

This doesn't run since the subquery returns more than one row. Any help is much appreciated :)

Solution

  • Every time any of the team must have won or match tied so you can use the case expression in the SELECT clause itself

    SELECT case when Home_Score > Away_Score then Home_Team 
            when Home_Score < Away_Score then Away_Team
            else 'tie'
       end as winner_team,
       greatest(Home_Score ,Away_Score ) as winner_score
FROM match