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

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 students.id = student_id;

• if a column exists in multiple tables, it may be written as table.column

Giving names to tables

SELECT name, g.*
FROM   students s
JOIN   grades g ON s.id = g.student_id;

• can give names to tables, like a variable name in Java
• to specify all columns from a table, write table.*

Filtered join (JOIN with WHERE)

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

• 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')

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

• grade column sorts alphabetically, so grades better than B- are ones <= it

A suboptimal query

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

IMDb database

• database name is imdb on server webster.cs.washington.edu
  • also available, imdb_small with fewer records (for testing queries)
• other tables:
  • directors (id, first_name, last_name)
  • movies_directors (director_id, movie_id)
  • movies_genres (movie_id, genre)

IMDb query example

[stepp@webster ~]$ mysql -u stepp -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Server version: 5.0.45-log Source distribution

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)

PHP IMDB example

# connect to IMDB (substitute your user name / pw)
$db = mysql_connect("localhost", "USERNAME", "PASSWORD");
if (!$db) {
	die("Connect failed: " . mysql_error());
}
if (!mysql_select_db("imdb")) {
	die("Selecting DB failed: " . mysql_error());
}

# query the database
$query = "SELECT * FROM actors WHERE first_name = 'Ezekiel'";
$results = mysql_query($query);
if (!$results) {
	die("SQL query failed:\n$query\n" . mysql_error());
}

# loop through each actor
while ($row = mysql_fetch_array($results)) {
?>
	<li>
		<?= $row["last_name"] ?>, <?= $row["first_name"] ?>
	</li>
<?php
}
?>

Practice problem: Movie search

• Write a PHP script that connects to the imdb database on webster and searches for all movies whose names match a given prefix, displaying them as an HTML table. Assume that the prefix is a query string parameter passed into the script.
• Consider modifying the code so that, if only one movie matches, it will print the IDs of all actors who acted in that movie. (This isn't very useful, but we'll improve it next time.)

Practice problem: Cast list for a movie

Write a PHP script that, when given a movie, shows the names of all female actors that appeared in it. (To do this, you will need to perform an SQL query with join operations.)

Development strategy

• 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!!