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.

Related tables and keys

• primary key: a column guaranteed to be unique for each record (e.g. Lisa Simpson's ID 888)
• foreign key: a column in table A storing a primary key value from table B
  • (e.g. records in grades with student_id of 888 are Lisa's grades)
• normalizing: splitting tables to improve structure / redundancy (linked by unique IDs)

Querying multi-table databases

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

• What courses has Bart taken and gotten a B- or better?
• What courses have been taken by both Bart and Lisa?
• Who are all the teachers Bart has had?
• How many total students has Ms. Krabappel taught, and what are their names?

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: combines records from two or more tables if they satisfy certain conditions
• the ON clause specifies which records from each table are matched
• the rows are often linked by their key columns (id)

Join example

SELECT *
FROM students
JOIN grades ON id = student_id;

• table.column can be used to disambiguate column names:

  SELECT *
  FROM students
  JOIN grades ON students.id = grades.student_id;
  

Filtering columns in a join

SELECT name, course_id, grade
FROM students
JOIN grades ON id = student_id;

Filtered join (JOIN with WHERE)

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

• FROM / JOIN glue the proper tables together, and WHERE filters the results
• what goes in the ON clause, and what goes in WHERE?
  • ON directly links columns of the joined tables
  • WHERE sets additional constraints such as particular values (123, 'Bart')

What's wrong with this?

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

• The above query produces the same rows as the previous one, but it is poor style. Why?
• The JOIN ON clause is poorly chosen. It doesn't really say what connects a grades record to a students record.
  • They are related when they are for a student with the same id.
  • Filtering out by a specific ID or name should be done in the WHERE clause, not JOIN ON.

Giving names to tables

SELECT s.name, g.*
FROM students s
JOIN grades g ON s.id = g.student_id
WHERE g.grade <= 'C';

• can give names to tables, like a variable name in Java
• to specify all columns from a table, write table.*
• (grade column sorts alphabetically, so grades C or better are ones <= it)

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-';

• More than 2 tables can be joined, as shown above
• What does the above query represent?
• The names of all courses in which Bart has gotten a B- or better.

A suboptimal query

• Exercise: What courses have been taken by both Bart and Lisa?

• SELECT bart.course_id
  FROM grades bart
  JOIN grades lisa ON lisa.course_id = bart.course_id
  WHERE bart.student_id = 123
  AND lisa.student_id = 888;
  

• problem: requires us to know Bart/Lisa's Student IDs, and only spits back course IDs, not names.
• Write a version of this query that gets us the course names, and only requires us to know Bart/Lisa's names, not their IDs.

Improved query

• What courses have been taken by both Bart and Lisa?

  SELECT DISTINCT c.name
  FROM courses c
  JOIN grades g1 ON g1.course_id = c.id
  JOIN students bart ON g1.student_id = bart.id
  JOIN grades g2 ON g2.course_id = c.id
  JOIN students lisa ON g2.student_id = lisa.id
  WHERE bart.name = 'Bart'
  AND lisa.name = 'Lisa';
  

Practice queries

• What are the names of all teachers Bart has had?

  SELECT DISTINCT t.name
  FROM teachers t
  JOIN courses c ON c.teacher_id = t.id
  JOIN grades g ON g.course_id = c.id 
  JOIN students s ON s.id = g.student_id
  WHERE s.name = 'Bart';
  

• How many total students has Ms. Krabappel taught, and what are their names?

  SELECT DISTINCT s.name
  FROM students s
  JOIN grades g ON s.id = g.student_id
  JOIN courses c ON g.course_id = c.id
  JOIN teachers t ON t.id = c.teacher_id
  WHERE t.name = 'Krabappel';
  

Designing a query

• Figure out the proper SQL queries in the following way:
  • Which table(s) contain the critical data? (FROM)
  • Which columns do I need in the result set? (SELECT)
  • How are tables connected (JOIN) and values filtered (WHERE)?
• Test on a small data set (imdb_small).
• Confirm on the real data set (imdb).
• Try out the queries first in the MySQL console.
• Write the PHP code to run those same queries.
  • Make sure to check for SQL errors at every step!!

Example imdb database

• also available, imdb_small with fewer records (for testing queries)

IMDb table relationships / ids

IMDb practice queries

• What are the names of all movies released in 1995?
• How many people played a part in the movie "Lost in Translation"?
• What are the names of all the people who played a part in the movie "Lost in Translation"?
• Who directed the movie "Fight Club"?
• How many movies has Clint Eastwood directed?
• What are the names of all movies Clint Eastwood has directed?
• What are the names of all directors who have directed at least one horror film?
• What are the names of every actor who has appeared in a movie directed by Christopher Nolan?

Issuing SQL commands directly in MySQL

SHOW DATABASES;
USE database;
SHOW TABLES;

• connect to Webster in an SSH program like PuTTY, then type:

$ 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>

• table defines the overall table, tr each row, and td each cell's data
• tables are useful for displaying large row/column data sets
• NOTE: tables are sometimes used by novices for web page layout, but this is not proper semantic HTML and should be avoided

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>

• th cells in a row are considered headers; by default, they appear bold
• a caption at the start of the table labels its meaning

Styling tables

table { border: 2px solid black; caption-side: bottom; }
tr { font-style: italic; }
td { background-color: yellow; text-align: center; width: 30%; }

• all standard CSS styles can be applied to a table, row, or cell
• table specific CSS properties:
  • border-collapse, border-spacing, caption-side, empty-cells, table-layout

The border-collapse property

table, td, th { border: 2px solid black; }
table { border-collapse: collapse; }

• by default, the overall table has a separate border from each cell inside
• the border-collapse property merges these borders into one

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>

• colspan makes a cell occupy multiple columns; rowspan multiple rows
• text-align and vertical-align control where the text appears within a cell

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>

.urgent {
	background-color: pink;
}
.highlight {
	background-color: yellow;
}

• col tag can be used to define styles that apply to an entire column (self-closing)
• colgroup tag applies a style to a group of columns (NOT self-closing)

Don't use tables for layout!

• (borderless) tables appear to be an easy way to achieve grid-like page layouts
  • many "newbie" web pages do this (including many UW CSE web pages...)
• but, a table has semantics; it should be used only to represent an actual table of data
• instead of tables, use divs, widths/margins, floats, etc. to perform layout

• tables should not be used for layout!
• Tables should not be used for layout!!
• TABLES SHOULD NOT BE USED FOR LAYOUT!!!
• TABLES SHOULD NOT BE USED FOR LAYOUT!!!!