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.