Sequence help

Databases
1

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.

2

Still trying to figure this out and running queries looking at the results. This query makes me think that I don’t need a movieSequence because the third movie in a series has no way to link to the second.

SELECT * FROM movie AS child 
LEFT JOIN movie AS parent 
ON child.movieParent = parent.movieUPC;

After seeing the results the movieParent seems to make more sense if it was the movie before it in the series, not the first movie. Curious to any thoughts or suggestions.

3

Well solved my problem, I ended up needing the sequence column after all, since the first movie doesn’t have a movie before it I needed it for my where clause to select the first movie and any sequels that fit the criteria.


select * from movie 
LEFT JOIN movieNight 
USING (movieUPC) 
LEFT JOIN movieNight as sequel 
ON sequel.movieUPC = movie.movieParent 
WHERE movie.movieSequence = 1 
AND movieNight.dateViewed IS NULL 
OR sequel.dateViewed IS NOT NULL 
AND movieNight.dateViewed IS NULL;

I would love suggestions on maybe making it more compact, didn’t know if there is shorthand for the where statement with the Null and not Null statements.