---
title: 'SQL語法學習心得-4'
disqus: hackmd
---
SQL語法學習心得
第四篇(JOIN)
===
[線上SQL測試網站](https://sqlzoo.net/wiki/SELECT_basics/zh)
---
3 table
---
JOIN
---
**id and matchid is the join index of two table**
1. show the player, teamid, stadium and mdate for every German goal
```sql
SELECT player, teamid, stadium, mdate
FROM game JOIN goal ON (id=matchid)
WHERE teamid = 'GER'
```
----
2. Show the team1, team2 and player for every goal scored by a player called Mario
```sql
SELECT team1, team2, player
FROM game JOIN goal ON (id=matchid)
WHERE player LIKE 'Mario%'
```
----
3. Show player, teamid, coach, gtime for all goals scored in the first 10 minutes
```sql
SELECT player, teamid, coach, gtime
FROM goal JOIN eteam on (teamid=id)
WHERE gtime<=10
```
----
Catious for same index in different table
---
If one col name appears in two table, we must specify
==table.col== to avoid error.
List the dates of the matches and the name of the team in which 'Fernando Santos' was the team1 coach.
```sql=
SELECT mdate,teamname
FROM game JOIN eteam ON (team1=eteam.id)
WHERE coach = 'Fernando Santos'
```
Since `id` is in both game and eteam table, we must specify `eteam.id`
---
Difficult Question
---
1. show the name of all players who scored a goal *==against Germany==*
```sql
SELECT DISTINCT player
FROM game JOIN goal ON matchid = id
WHERE (team1='GER' OR team2='GER') AND teamid != 'GER'
```
> use ==DISTINCT== to filter out duplicate query
> Someone might score against Germany in different games
> Also teamid cannot be Germany :+1:
----
2. Show teamname and the total number of goals scored.
```sql
SELECT teamname, COUNT(player)
FROM eteam JOIN goal ON id=teamid
GROUP BY teamname
```
----
GROUP BY Application (Tricky)
---
1. every match involving 'POL', show the matchid, date and the number of goals scored.
```sql
SELECT matchid,mdate, COUNT(player)
FROM game JOIN goal ON matchid = id
WHERE (team1 = 'POL' OR team2 = 'POL')
GROUP BY matchid, mdate
```
2. every match where 'GER' scored, show matchid, match date and the number of goals scored by 'GER'
```sql
SELECT matchid, mdate, COUNT(player)
FROM game JOIN goal ON matchid = id
WHERE teamid = 'GER'
GROUP BY matchid, mdate
```
Two example above: ==GROUP BY with 2 indexes==
----
CASE - WHEN - THEN - END
===
If it was a team 1 goal then a 1 appears in score 1, 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. Example output:
```sql
SELECT game.mdate, game.team1,
SUM(CASE WHEN goal.teamid = game.team1 THEN 1 ELSE 0 END) score1,
game.team2,
SUM(CASE WHEN goal.teamid = game.team2 THEN 1 ELSE 0 END) score2
FROM game LEFT JOIN goal ON matchid = id
GROUP BY mdate, team1, team2
```
NEW Command : **LEFT JOIN** will be introduce later
---
## More tutorial / note
1. [my coding-blog](fatcatcat-lab.blogspot.com)
2. [my movie-blog](fatcatcat-movie.blogspot.com)
###### tags: `SQL `beginner` `tutorial`
Sign in with Wallet
Connect another wallet