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.

Movie
movieUPC (PK)
movieTitle

movieGenre
movieUPC (PK)
genreName (PK)

movieNight
dateViewed (PK)
movieUPC

movieViewer
dateViewed (PK)
viewer (PK)

viewer
viewerID (PK)
viewerFname
favGenre

  1. The movie table stores the attributes of the movie
  2. A movie can have more than one genre
  3. movieNight is when a movie was watched
  4. viewer is the people that can watch a movie on movieNight
  5. 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:
Code MySQL:
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:
Code MySQL:
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;