I have a small database and this question is in regards to just five of the tables, I am going to list them with just the basics and primary keys.
- The movie table stores the attributes of the movie
- A movie can have more than one genre
- movieNight is when a movie was watched
- viewer is the people that can watch a movie on movieNight
- movieViewer is to store the many viewers for a single movieNight relation
I was trying to find all movies that match a particular viewers favGenre by joining tables. I ran into a problem joining movie and movieNight though:
SELECT * FROM movie LEFT JOIN movieNight ON movie.movieUPC = movieNight.movieUPC\\G
The problem I had was that if a movie wasn't watched yet I obviously got NULL values for dateViewed, so I couldn't link back to the viewer table.
While messing around I, from a typo, accidentally found a way that works, but was left even more confused, because I don't see why it should work:
SELECT movieTitle, genreName AS 'Anna''s Favorite Genre' FROM viewer
RIGHT JOIN movieViewer
ON viewer.viewerID = movieViewer.viewerID
RIGHT JOIN movieNight
ON movieViewer.dateViewed = movieNight.dateViewed
LEFT JOIN movie
ON movieNight.movieUPC = movieNight.movieUPC -- What is this line doing?
LEFT JOIN movieGenre
ON movie.movieUPC = movieGenre.movieUPC
WHERE viewerFname = 'Anna'
AND genreName = favGenre;