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,
CREATE TABLE movieNight (
dateViewed datetime NOT NULL PRIMARY KEY,
movieUPC char(15) NOT NULL
INSERT INTO movie
('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
('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.
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.
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
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.