CODEWITHSUNDEEP
mysqlsql
kelcifer027
kelcifer027

Reputation: 23

How to use JOIN with Conditions

In MySQL on the command-line, I am trying to print the names of teams that have scored more than 3 goals in a single game, whether it was the home team, the away team, or both.

I have two relations:

Team
+---------+-----------+------+----+
| name    | shortName | abbr | id |
+---------+-----------+------+----+

Game
+---------+--------------+--------------+------------+------------+
| game_id | home_team_id | away_team_id | score_home | score_away |
+---------+--------------+--------------+------------+------------+

(home_team_id and away_team_id are both foreign keys for Team.id)

I started by trying to find the game_ids where a team had scored more than 3 goals:

> SELECT game_id, score_home, score_away 
FROM Game 
WHERE score_home > 3 OR score_away > 3;
+---------+------------+------------+
| game_id | score_home | score_away |
+---------+------------+------------+
|       7 |          6 |          2 |
|      35 |          3 |          4 |
|      70 |          4 |          1 |
|      71 |          2 |          5 |
|      84 |          5 |          1 |
|      88 |          6 |          2 |
|      97 |          1 |          5 |
|     103 |          6 |          1 |
+---------+------------+------------+

So I'm pretty sure there should only be about 8 teams at most that have scored more than 3 goals. I then tried INNER JOINS but I'm not quite sure how this works with two different foreign keys and conditions but this was my attempt:

> SELECT Team.name as Team_Name, Game.game_id, Game.score_home, Game.score_away 
    -> FROM Team
    -> INNER JOIN Game ON Team.id=home_team_id OR Team.id=away_team_id
    -> WHERE score_home > 3 OR score_away > 3;
+-------------------+---------+------------+------------+
| Team_Name         | game_id | score_home | score_away |
+-------------------+---------+------------+------------+
| Arsenal           |      71 |          2 |          5 |
| Everton           |       7 |          6 |          2 |
| Manchester City   |      70 |          4 |          1 |
| Manchester City   |      84 |          5 |          1 |
| Manchester City   |     103 |          6 |          1 |
| Norwich City      |      88 |          6 |          2 |
| Tottenham Hotspur |      70 |          4 |          1 |
| Tottenham Hotspur |      97 |          1 |          5 |
| Newcastle United  |      88 |          6 |          2 |
| Newcastle United  |     103 |          6 |          1 |
| West Ham United   |      35 |          3 |          4 |
| Leicester City    |      71 |          2 |          5 |
| Sunderland        |       7 |          6 |          2 |
| Bournemouth       |      35 |          3 |          4 |
| Bournemouth       |      84 |          5 |          1 |
| Bournemouth       |      97 |          1 |          5 |
+-------------------+---------+------------+------------+

It's giving me both the home team name and the away team when I only want the team that scored higher than 3 points. Please help.

Upvotes: 1

Views: 74

Answers (3)

mkRabbani
mkRabbani

Reputation: 16908

This following query will return Home and Away in separate row if both scored more than 3 goals in a single match-

SELECT 
T.Name as [Team_Name],
A.Team_Type,
A.Game_id,
A.Score
FROM
(
    SELECT 'Home' AS [Team_Type],game_id AS Game_id,home_team_id as team_ID, score_home as Score WHERE score_home>3
    UNION ALL
    SELECT 'Away' AS [Team_Type], game_id AS Game_id,away_team_id as team_ID, score_away as Score WHERE score_away>3
)A
INNER JOIN Team T 
ON T.id = A.team_ID
ORDER BY A.Game_id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

One approach uses correlated subqueries:

select t.name
from team t
where exists (select 1
              from game g
              where g.home_team_id = t.id and
                    g.score_home > 3
             ) and
      exists (select 1
              from game g
              where g.away_team_id = t.id and
                    g.score_away > 3
             ) ;

This query can take advantage of indexes on game(home_team_id, score_home) and game(away_team_id, score_away).

Upvotes: 0

sticky bit
sticky bit

Reputation: 37472

Get all the team IDs in one column in a subquery using UNION and then join the teams to it.

SELECT t.name
       FROM (SELECT g.home_team_id team_id
                    FROM game g
                    WHERE score_home > 3
             UNION
             SELECT g.away_team_id team_id
                    FROM game g
                    WHERE score_away > 3) x
            INNER JOIN team t
                       ON t.id = x.team_id;

Upvotes: 1

Related Questions