Trying to fine tune a database I created for a school project by adding a movie sequel column and what sequel the movie is, my two tables are as follows with just the necessities and some data for a visual:
CREATE TABLE movie (
movieUPC char(15) NOT NULL PRIMARY KEY,
movieTitle char(100) NOT NULL,
movieParent char(15),
movieSequence int(3)
);
CREATE TABLE movieNight (
dateViewed datetime NOT NULL PRIMARY KEY,
movieUPC char(15) NOT NULL
);
INSERT INTO movie
VALUES
('024543088172', '28 Days Later', NULL, NULL),
('948569584378', 'Dog Day Afternoon', NULL, NULL),
('024543469902', '28 Weeks Later', '024543088172', 2),
('7348739745874', 'Exiled', NULL, NULL),
('025192213120', 'Back To The Future', NULL, NULL),
('025192213120a', 'Back To The Future II', '025192213120', 2),
('948569583496', 'Kiss Kiss, Bang Bang', NULL, NULL),
('025192213120b', 'Back To The Future III', '025192213120', 3)
;
INSERT INTO movieNight
VALUES
('2011-03-03 14:33:27', '7348739745874'),
('2011-03-23 16:01:13', '025192213120'),
('2011-03-24 12:01:13', '948569584378')
;
How would I go about selecting movies that haven’t been watched and selecting the parent movie or the next one in sequence if the first film has been watched? I know how to see what movies haven’t been watched by left joining movie on movieNight and seeing if dateViewed is NULL, but now that I added the two new columns I’m not sure what I should do.