Web Programming Step by Step, 2nd Edition

Lecture 15: Multi-table SQL Queries (Joins)

Reading: 13.3 - 13.5

Except where otherwise noted, the contents of this document are Copyright 2012 Marty Stepp, Jessica Miller, and Victoria Kirst. All rights reserved. Any redistribution, reproduction, transmission, or storage of part or all of the contents in any form is prohibited without the author's expressed written permission.

Valid HTML5 Valid CSS

13.3: Multi-table Queries

Related tables and keys

students
idnameemail
123Bartbart@fox.com
456Milhousemilhouse@fox.com
888Lisalisa@fox.com
404Ralphralph@fox.com
courses
idnameteacher_id
10001Computer Science 1421234
10002Computer Science 1435678
10003Computer Science 190M9012
10004Informatics 1001234
grades
student_idcourse_idgrade
12310001B-
12310002C
45610001B+
88810002A+
88810003A+
40410004D+
teachers
idname
1234Krabappel
5678Hoover
9012Stepp

Querying multi-table databases

When we have larger datasets spread across multiple tables, we need queries that can answer high-level questions such as:

To do this, we'll have to join data from several tables in our SQL queries.

Joining with ON clauses

SELECT column(s)
FROM table1
JOIN table2 ON condition(s)
...
JOIN tableN ON condition(s);
SELECT *
FROM students
JOIN grades ON id = student_id;

Join example

SELECT *
FROM students
JOIN grades ON id = student_id;
idnameemailstudent_idcourse_idgrade
123Bartbart@fox.com12310001B-
123Bartbart@fox.com12310002C
404Ralphralph@fox.com40410004D+
456Milhousemilhouse@fox.com45610001B+
888Lisalisa@fox.com88810002A+
888Lisalisa@fox.com88810003A+

Filtering columns in a join

SELECT name, course_id, grade
FROM students
JOIN grades ON id = student_id;
namecourse_idgrade
Bart10001B-
Bart10002C
Ralph10004D+
Milhouse10001B+
Lisa10002A+
Lisa10003A+

Filtered join (JOIN with WHERE)

SELECT name, course_id, grade
FROM students
JOIN grades ON id = student_id
WHERE name = 'Bart';
namecourse_idgrade
Bart10001B-
Bart10002C

What's wrong with this?

SELECT name, id, course_id, grade
FROM students
JOIN grades ON id = 123
WHERE id = student_id;
nameidcourse_idgrade
Bart12310001B-
Bart12310002C

Giving names to tables

SELECT s.name, g.*
FROM students s
JOIN grades g ON s.id = g.student_id
WHERE g.grade <= 'C';
namestudent_idcourse_idgrade
Bart12310001B-
Bart12310002C
Milhouse45610001B+
Lisa88810002A+
Lisa88810003A+

Multi-way join

SELECT c.name
FROM courses c
JOIN grades g ON g.course_id = c.id
JOIN students bart ON g.student_id = bart.id
WHERE bart.name = 'Bart' AND g.grade <= 'B-';
name
Computer Science 142

A suboptimal query

Improved query

Practice queries

Designing a query

Example imdb database

actors
idfirst_namelast_namegender
433259WilliamShatnerM
797926BritneySpearsF
831289SigourneyWeaverF
...
movies
idnameyearrank
112290Fight Club19998.5
209658Meet the Parents20007
210511Memento20008.7
...
roles
actor_idmovie_idrole
433259313398Capt. James T. Kirk
433259407323Sgt. T.J. Hooker
797926342189Herself
...
movies_genres
movie_idgenre
209658Comedy
313398Action
313398Sci-Fi
...
directors
idfirst_namelast_name
24758DavidFincher
66965JayRoach
72723WilliamShatner
...
movies_directors
director_idmovie_id
24758112290
66965209658
72723313398
...

IMDb table relationships / ids

IMDb tables tree

IMDb practice queries

Issuing SQL commands directly in MySQL

SHOW DATABASES;
USE database;
SHOW TABLES;
$ mysql -u yourusername -p
Password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> USE world;
Database changed

mysql> SHOW TABLES;
+-----------+
| cities    | 
| countries | 
| languages | 
+-----------+
3 rows in set (0.00 sec)

IMDb query example

[stepp@webster ~]$ mysql -u myusername -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> use imdb_small;
Database changed

mysql> select * from actors where first_name like '%mick%';
+--------+------------+-----------+--------+
| id     | first_name | last_name | gender |
+--------+------------+-----------+--------+
|  71699 | Mickey     | Cantwell  | M      | 
| 115652 | Mickey     | Dee       | M      | 
| 470693 | Mick       | Theo      | M      | 
| 716748 | Mickie     | McGowan   | F      | 
+--------+------------+-----------+--------+
4 rows in set (0.01 sec)

HTML tables: <table>, <tr>, <td>

A 2D table of rows and columns of data (block element)

<table>
	<tr><td>1,1</td><td>1,2 okay</td></tr>
	<tr><td>2,1 real wide</td><td>2,2</td></tr>
</table>
1,11,2 okay
2,1 real wide2,2

Table headers, captions: <th>, <caption>

<table>
	<caption>My important data</caption>
	<tr><th>Column 1</th><th>Column 2</th></tr>
	<tr><td>1,1</td><td>1,2 okay</td></tr>
	<tr><td>2,1 real wide</td><td>2,2</td></tr>
</table>
My important data
Column 1Column 2
1,11,2 okay
2,1 real wide2,2

Styling tables

table { border: 2px solid black; caption-side: bottom; }
tr { font-style: italic; }
td { background-color: yellow; text-align: center; width: 30%; }
My important data
Column 1Column 2
1,11,2 okay
2,1 real wide2,2

The border-collapse property

table, td, th { border: 2px solid black; }
table { border-collapse: collapse; }
Without border-collapse
Column 1Column 2
1,11,2
2,12,2
With border-collapse
Column 1Column 2
1,11,2
2,12,2

The rowspan and colspan attributes

<table>
	<tr><th>Column 1</th><th>Column 2</th><th>Column 3</th></tr>
	<tr><td colspan="2">1,1-1,2</td>
		<td rowspan="3">1,3-3,3</td></tr>
	<tr><td>2,1</td><td>2,2</td></tr>
	<tr><td>3,1</td><td>3,2</td></tr>
</table>

Column styles: <col>, <colgroup>

<table>
	<col class="urgent" />
	<colgroup class="highlight" span="2"></colgroup>
	
	<tr><th>Column 1</th><th>Column 2</th><th>Column 3</th></tr>
	<tr><td>1,1</td><td>1,2</td><td>1,3</td></tr>
	<tr><td>2,1</td><td>2,2</td><td>2,3</td></tr>
</table>

Don't use tables for layout!