SQL語法學習心得
第四篇(JOIN)

線上SQL測試網站

---

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

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

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

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.

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

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

---

2. Show teamname and the total number of goals scored.

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.

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'

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:

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
2. my movie-blog

tags: SQL beginner tutorial`