Adding IDs to join table for many-to-many in SQLite

Question

I am using SQLite Studio 3.3 to work with a simple video database. These are three of the tables I am working with.

-- Table: Videos
DROP TABLE IF EXISTS Videos;

CREATE TABLE Videos (
    id           INTEGER PRIMARY KEY ASC AUTOINCREMENT
                         UNIQUE
                         NOT NULL,
    vid_title    TEXT    NOT NULL
                         UNIQUE,
    vid_rel_date DATE
);

-- Table: People
DROP TABLE IF EXISTS People;

CREATE TABLE People (
    id                INTEGER PRIMARY KEY AUTOINCREMENT
                              NOT NULL
                              UNIQUE,
    person_name_first TEXT    NOT NULL,
    person_name_last  TEXT
);

-- Table: _Videos_People
DROP TABLE IF EXISTS _Videos_People;

CREATE TABLE _Videos_People (
    id        INTEGER PRIMARY KEY AUTOINCREMENT
                      UNIQUE
                      NOT NULL,
    vid_id    INTEGER REFERENCES Videos (id),
    person_id INTEGER REFERENCES People (id) 
);

I have these statements to add a video and a person to the database and to get both IDs.

INSERT OR IGNORE INTO Videos (
                       vid_title,
                       vid_rel_date
                   )
                   VALUES (
                       'The Call Of The Wild',
                       '2020'
                   );

INSERT OR IGNORE INTO People (
                       person_name_first,
                       person_name_last
                   )
                   VALUES (
                       'Harrison',
                       'Ford'
                   );
                   
SELECT id
  FROM Videos
  WHERE vid_title = 'The Call Of The Wild'
    AND vid_rel_date = '2020';

SELECT id
  FROM People
  WHERE person_name_first = 'Harrison'
    AND person_name_last = 'Ford';

This is where I am stuck. Once I have these two IDs, how do I add them to the _Videos_People join table so that the person is associated with the video?

I found this but I don't know how to use it for my situation (or if I should).

If I am going about this completely wrong, I am open to suggestions. I am new at this so I appreciate any help.

score 0 · Accepted Answer · answered May 14 '21 at 16:22

0

You can use this INSERT statement:

INSERT INTO _Videos_People (vid_id, person_id) VALUES (
  (SELECT id FROM Videos WHERE vid_title = 'The Call Of The Wild' AND vid_rel_date = '2020'),
  (SELECT id FROM People WHERE person_name_first = 'Harrison' AND person_name_last = 'Ford')
);

See the demo.

answered May 14 '21 at 16:22

forpas

160,666
10
38
76

Thanks for the quick response and that "db<>fiddle" site looks like something I could really use. – GBMedusa May 14 '21 at 20:40

Adding IDs to join table for many-to-many in SQLite

1 Answers1