> For the complete documentation index, see [llms.txt](https://dshub.gitbook.io/ds-hub/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://dshub.gitbook.io/ds-hub/sql/sql-practice/popular-websites-for-sql-practice/sqlzoo/soccer-football-tables/join-tutorial.md).
# 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.
| mdate | team1 | score1 | team2 | score2 |
|---|
| 1 July 2012 | ESP | 4 | ITA | 0 |
| 10 June 2012 | ESP | 1 | ITA | 1 |
| 10 June 2012 | IRL | 1 | CRO | 3 |
| ... | | | | |
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
```
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://dshub.gitbook.io/ds-hub/sql/sql-practice/popular-websites-for-sql-practice/sqlzoo/soccer-football-tables/join-tutorial.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.