Difference between revisions of "SELECT .. JOIN"

From SQLZOO
Jump to: navigation, search
Line 26: Line 26:
 
<caption align='center'>'''eloix'''</caption>
 
<caption align='center'>'''eloix'''</caption>
 
<tr><th align='center'>'''name'''</th><th align='center'>'''cod'''</th></tr>
 
<tr><th align='center'>'''name'''</th><th align='center'>'''cod'''</th></tr>
<tr><td>'Txadinha'</td><td align=1>Athens</td></tr>
+
<tr><td>Txadinha</td><td align=1>Athens</td></tr>
<tr><td>'Sao Filipe'</td><td align=2>London</td></tr>
+
<tr><td>Sao Filipe</td><td align=2>London</td></tr>
<tr><td>'Fazenda'</td><td align=3>Athens</td></tr>
+
<tr><td>Fazenda</td><td align=3>Athens</td></tr>
<tr><td>'Plateau'</td><td align=4>Beijing</td></tr>
+
<tr><td>Plateau</td><td align=4>Beijing</td></tr>
 
</table>
 
</table>
 
</td></table>
 
</td></table>

Revision as of 22:46, 8 February 2013

The SELECT .. JOIN statement

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

games
yrcity
1896Athens
1948London
2004Athens
2008Beijing
2012London
city
namecountry
SydneyAustralia
AthensGreece
BeijingChina
LondonUK
eloix
namecod
TxadinhaAthens
Sao FilipeLondon
FazendaAthens
PlateauBeijing
schema:scott
 DROP TABLE games;
DROP TABLE city
 CREATE TABLE games(
  yr INTEGER,
  city VARCHAR(20));
INSERT INTO games VALUES (1896,'Athens');
INSERT INTO games VALUES (1948,'London');
INSERT INTO games VALUES (2004,'Athens');
INSERT INTO games VALUES (2008,'Beijing');
INSERT INTO games VALUES (2012,'London');
CREATE TABLE city (
   name VARCHAR(20),
   country VARCHAR(20));
INSERT INTO city VALUES ('Sydney','Australia');
INSERT INTO city VALUES ('Athens','Greece');
INSERT INTO city VALUES ('Beijing','China');
INSERT INTO city VALUES ('London','UK');
CREATE TABLE eloix (
   name VARCHAR(20),
   cod INT);
INSERT INTO eloix VALUES ('Txadinha',1);
INSERT INTO eloix VALUES ('Sao Filipe',2);
INSERT INTO eloix VALUES ('Fazenda',3);
INSERT INTO eloix VALUES ('Plateau',4);

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

SELECT games.yr, city.country
  FROM scott.games JOIN scott.city
       ON (games.city = city.name)
SELECT games.yr, city.country
  FROM games JOIN city
       ON (games.city = city.name)


See also

Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense