SQL: Referencing Other Tables

Welcome to the Referencing Other Tables lesson!

This lesson is shown as static text below. However, it's designed to be used interactively. Click the button below to start!

Until this lesson, every table in the course has been an island: it hasn't related to other tables. In real systems, data relates to other data. To start with a simple example: people can own cats.
In a general-purpose programming language like JavaScript, we might represent that as: {name: 'Amir', cats: [{name: 'Ms. Fluff'}]}. The cats are stored as part of the person. Many SQL databases will allow us to store cats "inside" people in this way. However, if we use that style then we give up much of SQL's value.
Instead, we'll create two separate tables. Each person will have an integer ID. Each cat will have an integer owner_id holding the owner's ID. (Remember that executing an INSERT, CREATE, etc. returns [].)

exec(`
  CREATE TABLE people (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
  CREATE TABLE cats (owner_id INTEGER NOT NULL, name TEXT NOT NULL);
  INSERT INTO people (id, name) VALUES (100, 'Amir');
  INSERT INTO cats (owner_id, name) VALUES (100, 'Ms. Fluff');
`);

Result:

[]

Note: this code example reuses elements (variables, etc.) defined in earlier examples.
>
exec(`SELECT id FROM people WHERE name = 'Amir';`);
Result:
[{id: 100}]
Note: this code example reuses elements (variables, etc.) defined in earlier examples.
>
exec(`SELECT name FROM cats WHERE owner_id = 100;`);
Result:
[{name: 'Ms. Fluff'}]
Note: this code example reuses elements (variables, etc.) defined in earlier examples.
>
exec(`SELECT name FROM cats WHERE owner_id = 101;`);
Result:
[]
We can combine the person and cat queries in a JavaScript function.

Note: this code example reuses elements (variables, etc.) defined in earlier examples.

function findCatNames(personName) {
  const allCatNames = [];
  const people = exec(`SELECT id FROM people WHERE name = ?`, [personName]);
  for (const person of people) {
    const cats = exec(`SELECT name FROM cats WHERE owner_id = ?`, [person.id]);
    for (const cat of cats) {
      allCatNames.push(cat.name);
    }
  }
  return allCatNames;
}
findCatNames('Amir');

Result:

['Ms. Fluff']

One important note about tables that reference each other. We would never create separate tables named amir_cats, betty_cats, etc. Instead, we leave all of the cats in one table. When we need to find a certain person's cats, we let the ID columns guide us.
That's part of a more general rule of SQL databases: the application itself never changes the database's structure. The application doesn't create or drop tables or change columns. We set the database schema up in advance. Then the application creates, updates, and deletes rows.
Having only one cats table doesn't limit us. We can select a person's cats by owner_id, even when there are multiple people with different cats:

exec(`
  CREATE TABLE people (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
  CREATE TABLE cats (owner_id INTEGER NOT NULL, name TEXT NOT NULL);
  -- Amir owns Ms. Fluff.
  INSERT INTO people (id, name) VALUES (100, 'Amir');
  INSERT INTO cats (owner_id, name) VALUES (100, 'Ms. Fluff');
  -- Betty owns Keanu.
  INSERT INTO people (id, name) VALUES (101, 'Betty');
  INSERT INTO cats (owner_id, name) VALUES (101, 'Keanu');
`);

// The database holds both Amir's and Betty's cats.
// Selecting by owner ID lets us distinguish between them.
exec(`SELECT name FROM cats WHERE owner_id = ?`, [100]);

Result:

[{name: 'Ms. Fluff'}]

Here's a code problem:
Write a function to find the names of all toys owned by a certain cat.
exec(`
CREATE TABLE cats (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE toys (cat_id INTEGER NOT NULL, name TEXT NOT NULL);
INSERT INTO cats (name) VALUES ('Ms. Fluff');
INSERT INTO cats (name) VALUES ('Keanu');
INSERT INTO toys (cat_id, name) VALUES (1, 'Birdo');
INSERT INTO toys (cat_id, name) VALUES (2, 'Mouser');
`);
function findToys(catName) {
const allToyNames = [];
const cats = exec(`SELECT id FROM cats WHERE name = ?`, [catName]);
for (const cat of cats) {
const toys = exec(`SELECT name FROM toys WHERE cat_id = ?`, [cat.id]);
for (const toy of toys) {
allToyNames.push(toy.name);
}
}
return allToyNames;
}
findToys('Ms. Fluff');
Goal:
['Birdo']
Yours:
['Birdo']
Show Author's Answer