Revision as of 08:20, 21 August 2014

The SELECT .. JOIN statement

Sometimes you need to access two or more tables to get the data required.

games

yr

city

1896

Athens

1948

London

2004

Athens

2008

Beijing

2012

London

city

name

country

Sydney

Australia

Athens

Greece

Beijing

China

London

UK

schema:scott

DROPTABLE games;
DROPTABLE city

CREATETABLE games(
yr INTEGER,
city VARCHAR(20));
INSERTINTO games VALUES(1896,'Athens');
INSERTINTO games VALUES(1948,'London');
INSERTINTO games VALUES(2004,'Athens');
INSERTINTO games VALUES(2008,'Beijing');
INSERTINTO games VALUES(2012,'London');
CREATETABLE city (
name VARCHAR(20),
country VARCHAR(20));
INSERTINTO city VALUES('Sydney','Australia');
INSERTINTO city VALUES('Athens','Greece');
INSERTINTO city VALUES('Beijing','China');
INSERTINTO city VALUES('London','UK');

You can use a JOIN to get results from two or more related tables.
In this example each row of the table games is related to a row of the table city.
If you want to find the country where the games took place you must JOIN the games table to the city table on the common field that is games.city and city.name