# JOIN Tutorial [Link](https://sqlzoo.net/wiki/The_JOIN_operation) ## game Table | id | mdate | stadium | team1 | team2 | | ---- | ------------ | ------------------------- | ----- | ----- | | 1001 | 8 June 2012 | National Stadium, Warsaw | POL | GRE | | 1002 | 8 June 2012 | Stadion Miejski (Wroclaw) | RUS | CZE | | 1003 | 12 June 2012 | Stadion Miejski (Wroclaw) | GRE | CZE | | 1004 | 12 June 2012 | National Stadium, Warsaw | POL | RUS | | ... | | | | | ## goal Table | matchid | teamid | player | gtime | | ------- | ------ | -------------------- | ----- | | 1001 | POL | Robert Lewandowski | 17 | | 1001 | GRE | Dimitris Salpingidis | 51 | | 1002 | RUS | Alan Dzagoev | 15 | | 1002 | RUS | Roman Pavlyuchenko | 82 | | ... | | | | ## eteam Table | id | teamname | coach | | --- | -------------- | ---------------- | | POL | Poland | Franciszek Smuda | | RUS | Russia | Dick Advocaat | | CZE | Czech Republic | Michal Bilek | | GRE | Greece | Fernando Santos | | ... | | | 1. The first example shows the goal scored by a player with the last name 'Bender'. The `*` says to list all the columns in the table - a shorter way of saying `matchid, teamid, player, gtime` \ Modify it to show the *matchid* and *player* name for all goals scored by Germany. To identify German players, check for: `teamid = 'GER'` ```sql SELECT matchid, player FROM goal WHERE teamid = 'GER' ``` 2. From the previous query you can see that Lars Bender's scored a goal in game 1012. Now we want to know what teams were playing in that match.\ \ Notice that the column `matchid` in the `goal`table corresponds to the `id` column in the `game` table. We can look up information about game 1012 by finding that row in the game table.\ \ Show id, stadium, team1, team2 for just game 1012 ```sql SELECT id, stadium, team1, team2 FROM game WHERE matchid = 1012 ``` 3. Modify it to show the player, teamid, stadium and mdate for every German goal. ```sql SELECT player, teamid, stadium, mdate FROM game JOIN goal ON (id=matchid) AND teamid = 'GER' ``` 4. Use the same `JOIN` as in the previous question. Show the team1, team2 and player for every goal scored by a player called Mario `player LIKE 'Mario%'` ```sql SELECT gm.team1, gm.team2, go.player FROM game gm JOIN goal go ON (gm.id=go.matchid) WHERE player LIKE 'Mario%' ``` \ 5\. The table `eteam` gives details of every national team including the coach. You can `JOIN` `goal` to `eteam`using the phrase `goal JOIN eteam on teamid=id`\ Show `player`, `teamid`, `coach`, `gtime` for all goals scored in the first 10 minutes `gtime<=10` ```sql SELECT go.player, go.teamid, et.coach, go.gtime FROM goal go JOIN eteam et ON (go.teamid = et.id) WHERE go.gtime <= 10 ``` 6. To `JOIN` `game` with `eteam` you could use either\ `game JOIN eteam ON (team1=eteam.id)` or `game JOIN eteam ON (team2=eteam.id)` Notice that because `id` is a column name in both `game`and `eteam` you must specify `eteam.id` instead of just `id`\ List the dates of the matches and the name of the team in which 'Fernando Santos' was the team1 coach. ```sql SELECT gm.mdate, et.teamname FROM game gm JOIN eteam et ON (gm.team1 = et.id) WHERE et.coach = 'Fernando Santos' ``` 7. List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw' ```sql SELECT go.player FROM game gm JOIN goal go ON (gm.id=go.matchid) WHERE gm.stadium = 'National Stadium, Warsaw' ``` 8. The example query shows all goals scored in the Germany-Greece quarterfinal. Instead show the name of all players who scored a goal against Germany. {% hint style="info" %} Select goals scored only by non-German players in matches where GER was the id of either team1 or team2. You can use `teamid!='GER'` to prevent listing German players. You can use `DISTINCT` to stop players being listed twice. {% endhint %} ```sql SELECT DISTINCT go.player FROM game gm JOIN goal go ON gm.id=go.matchid AND (gm.team1 = 'GER' OR gm.team2 = 'GER') WHERE go.teamid!='GER' -- or -- WHERE go.teamid != 'GER' AND (gm.team1 = 'GER' OR gm.team2='GER') ``` 9. Show teamname and the total number of goals scored. ```sql SELECT et.teamname, COUNT(go.player) as goals_scored FROM eteam et JOIN goal go ON et.id = go.teamid GROUP BY et.teamname ``` 10. Show the stadium and the number of goals scored in each stadium. ```sql SELECT gm.stadium, COUNT(go.player) as goals_scored FROM game gm JOIN goal go ON (gm.id=go.matchid) GROUP BY gm.stadium ``` 11. For every match involving 'POL', show the matchid, date and the number of goals scored. ```sql SELECT go.matchid, gm.mdate, COUNT(go.player) as goals_scored FROM game gm JOIN goal go ON gm.id = go.matchid AND (gm.team1 = 'POL' OR gm.team2 = 'POL') GROUP BY go.matchid, gm.mdate -- or -- ON gm.id = go.matchid -- WHERE gm.team1 = 'POL' or gm.team2 = 'POL' ``` 12. For every match where 'GER' scored, show matchid, match date and the number of goals scored by 'GER' ```sql SELECT go.matchid, gm.mdate, COUNT(go.player) as goals_scored FROM game gm JOIN goal go ON gm.id = go.matchid AND go.teamid = 'GER' GROUP BY go.matchid, gm.mdate -- or -- ON goal.matchid = game.id -- WHERE teamid = 'GER' ``` 13. List every match with the goals scored by each team as shown. This will use "[CASE WHEN](https://sqlzoo.net/wiki/CASE)" which has not been explained in any previous exercises.
mdateteam1score1team2score2
1 July 2012ESP4ITA 0
10 June 2012ESP1ITA1
10 June 2012IRL1CRO3
...
Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1. Sort your result by mdate, matchid, team1 and team2. ```sql SELECT gm.mdate, gm.team1, SUM(CASE WHEN go.teamid = gm.team1 THEN 1 ELSE 0 END) AS score1, gm.team2, SUM(CASE WHEN go.teamid = gm.team2 THEN 1 ELSE 0 END) AS score2 FROM game gm LEFT JOIN goal go ON gm.id = go.matchid GROUP BY gm.mdate, gm.team1, gm.team2 ```