Old JOIN Tutorial

Contents

The Table Tennis Olympics Database

The table ttms shows the Olympic medal winners for Table Tennis (Men's Singles). The country of each winner is given by a three letter code. To get the actual country name you must JOIN this table to the country table.

The two tables country and ttms are ONE TO MANY. One country has many winners, each winner has only one country.

<img src='pics/ttms.png'/>

ttms

games

color

who

country

1988

gold

Yoo Nam-Kyu

KOR

1988

silver

Kim Ki Taik

KOR

..

..

..

..

country

id

name

ALG

Algeria

ARG

Argentina

...

...

KOR

Korea

..

..

How to do joins

The phrase FROM ttms JOIN country ON ttms.country=country.id represents the join of the tables ttms and country. This JOIN has one row for every medal winner. In addition to the ttms fields (games, color, who and country) it includes the details of the corresponding country (id, name ).

Summary

Show the athelete (who) and the country name for medal winners in 2000.

ttms(games,color,who,country)
country(id,name)

SELECT who, country.name
FROM ttms JOIN country
ON(ttms.country=country.id)WHERE games =2000

SELECT who, country.name
FROM ttms JOIN country
ON(ttms.country=country.id)WHERE games =2000

Show the who and the color of the medal for the medal winners from 'Sweden'.