To JOIN '''game''' with '''eteam''' you could use either <code>game JOIN eteam ON (team1=eteam.id)</code> or <code>game JOIN eteam ON (team2=eteam.id)</code>

+

To JOIN '''game''' with '''eteam''' you could use either<br/> <code>game JOIN eteam ON (team1=eteam.id)</code> or <code>game JOIN eteam ON (team2=eteam.id)</code>

Notice that because '''id''' is a column name in both '''game''' and '''eteam''' you must specify '''eteam.id''' instead of just '''id'''

Notice that because '''id''' is a column name in both '''game''' and '''eteam''' you must specify '''eteam.id''' instead of just '''id'''

Line 227:

Line 227:

<div class='qu'>

<div class='qu'>

−

This example uses <code>COUNT()</code>, <code>GROUP BY</code> and <code>SELECT within SELECT</code> to show teams that have scored more goals than England. Add <code>JOIN</code> and...

+

<p class='imper'>List the player for every goal scored in a game where the staium was 'National Stadium, Warsaw'</p>

−

+

−

<p class='imper'>Show '''stadium''' and '''count''' of goals for stadiums where the number of goals is larger than number of goals scored by Spanish players (teamid = 'ESP') during the entire championship.</p>

+

<source lang='sql' class='def'>

<source lang='sql' class='def'>

−

SELECT teamid, COUNT(*)

−

FROM goal

−

GROUP BY teamid

−

HAVING COUNT(*) >

−

(SELECT COUNT(*)

−

FROM goal WHERE teamid = 'ENG')

</source>

</source>

<source lang='sql' class='ans'>

<source lang='sql' class='ans'>

−

SELECT stadium, COUNT(*)

+

SELECT player

−

FROM game JOIN goal ON matchid = id

+

FROM goal JOIN game ON (id=matchid)

−

GROUP BY stadium

+

WHERE stadium = 'National Stadium, Warsaw'

−

HAVING COUNT(*) >=

+

−

(SELECT COUNT(*)

+

−

FROM goal WHERE teamid = 'ESP')

+

</source>

</source>

</div>

</div>

Revision as of 13:36, 29 September 2012

game

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

matchid

teamid

player

gtime

1001

POL

Robert Lewandowski

17

1001

GRE

Dimitris Salpingidis

51

1002

RUS

Alan Dzagoev

15

1001

RUS

Roman Pavlyuchenko

82

...

eteam

id

teamname

coach

POL

Poland

Franciszek Smuda

RUS

Russia

Dick Advocaat

CZE

Czech Republic

Michal Bilek

GRE

Greece

Fernando Santos

...

JOIN and UEFA EURO 2012

This tutorial introduces JOIN which allows you to use data from two or more tables. The tables contain all matches and goals from UEFA EURO 2012 Football Championship in Poland and Ukraine.

Summary

The first example shows the goal scored by 'Bender'.

Show matchid and player name for all goals scored by Germany.
teamid = 'GER'

SELECT*FROM goal
WHERE player LIKE'%Bender'

SELECT matchid, player
FROM goal
WHERE teamid LIKE'GER'

From the previous query you can see that Lars Bender's goal was scored in game 1012.
Notice that the column matchid in the goal table corresponds to the id column in the game table.

Show id, stadium, team1, team2 for game 1012

SELECT id,stadium,team1,team2
FROM game
WHERE stadium LIKE'%Warsaw%'

SELECT id,stadium,team1,team2
FROM game
WHERE id=1023

You can combine the two steps into a single query with a join. You will get all the game details and all the goal details if you use

SELECT *
FROM game JOIN goal ON (id=matchid)

Show the player, teamid and mdate and for every German goal. teamid='GER'