Populating a Junction Table

Question

I'm a SQLite and SQL beginner so please bear with me. I'm trying my hand a creating a simple podcast index database. Here's the schema for my EPISODE, CAST_MEMBER, and EPISODE_CAST tables.

CREATE TABLE EPISODE
(
    id                  INTEGER PRIMARY KEY,
    show_number         INTEGER UNIQUE NOT NULL,
    air_date            DATE UNIQUE NOT NULL,
    runtime             VARCHAR(255) NOT NULL
);

CREATE TABLE CAST_MEMBER
(
    id          INTEGER PRIMARY KEY,
    person_name VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE EPISODE_CAST
(
    episode_id      INTEGER NOT NULL,
    cast_member_id  INTEGER NOT NULL,
    PRIMARY KEY(episode_id, cast_member_id),
    FOREIGN KEY(episode_id) REFERENCES EPISODE(id),
    FOREIGN KEY(cast_member_id) REFERENCES CAST_MEMBER(id)
);

I'm trying to find a quick way to populate the EPISODE_CAST table. I know what all the relationships should be, I just don't want to have to write a million insert statements. For example, one cast member has been on every episode of the show so I naturally want 550 rows added to the table

episode_id | cast_member_id
===========================
1          | 1
2          | 1
3          | 1

… and so on.

So here's the query that I've written

INSERT INTO EPISODE_CAST (episode_id, cast_member_id) VALUES
   ((SELECT id FROM EPISODE WHERE show_number BETWEEN 1 AND 550),
   (SELECT id FROM CAST_MEMBER WHERE person_name='John Smith'));

But I'm only ever getting one row inserted. (The 1|1 row.) So I'm obviously missing something here. A loop of some sort…

Once I have that one cast member in there, I'd also like to do something similar with the other cast members. But for them I'll be using an IN clause with the set of all the episodes that person was in (most are non-contiguous).

Again, sorry for my naïvety. But thanks for any help.

Answer 1

Try a Cartesian or cross join. You can test the select clause separately to ensure it selects the desired data before adding it into the insert.

INSERT INTO EPISODE_CAST (episode_id, cast_member_id)
SELECT  episode.id AS episode_id,
        cast_member.id AS cast_member_id
FROM EPISODE CROSS JOIN CAST_MEMBER 
WHERE show_number BETWEEN 1 AND 550
AND   person_name='John Smith';