How to list only reviewed movies?

Question

I have a problem here. I created a database with different tables. I have a table named "movie" with 108 movies. This table includes columns "id, name, director, year, country", table named "reviews" which includes columns "movie_id" (movie_id links to the id of movie which was reviewed), "review_score" (1 to 5 points), "review (with review text)", "critics_id" (which links to the critics from critics-table), and also table named "critics" which icludes columns "critics_name" and "critics_id". The problem is that there is over 100 movies but only 10 of them are reviewed and I have to list only reviewed movies but reviews and movies are in different tables. When I try simple command SELECT name, review FROM movie, reviews - I get a huge list with repeating movies (see on picture) my table Is there any command which could list only reviewed movies and so that every reviewed movie would be in the list only once? Thanks beforehand!

Answer 1

This is where you use a join. You'd select from the movie table and join the reviews table, then filter by movies that have a joined review.

For example:

SELECT movie.*, review.review_score, review.review
FROM movie
LEFT JOIN reviews AS review
  ON review.movie_id = movie.movie_id -- assuming this column name is correct...
WHERE review.movie_id IS NOT NULL

You could also add another join to your critics table:

SELECT movie.*, review.review_score, review.review, critic.critics_name
FROM ....
LEFT JOIN critics AS critic
  ON critic.critics_id = review.critics_id
WHERE critic.critics_id IS NOT NULL

Please note that this example uses left join to highlight the way that the join works, in that if it doesn't find a match then the joined result will be null.

If you used an inner join instead, you wouldn't get any results that don't match the on clause, and as such you wouldn't need the WHERE id IS NOT NULL parts at all.

An inner joined example of the above would return movies that are reviewed, as well as the critic's name:

SELECT movie.*, review.review_score, review.review, critic.critics_name
FROM movie
INNER JOIN reviews AS review
  ON review.movie_id = movie.id
INNER JOIN critics AS critic
  ON critic.critics_id = review.critics_id

Also - I'm using AS to alias the plural table names to a singular name purely for semantic reasons. You could continue to use reviews and critics if you wanted to without the AS aliases.

Answer 2

Hope below query helps you:

SELECT
  m.name,
  c.critics_name,
  r.review_score,
  r.review_text
FROM movies m
  JOIN reviews r ON r.movie_id = m.id
  JOIN critics c ON c.critics_id = r.critics_id

for distinct movies change query as below:

SELECT DISTINCT m.name
FROM movies m
  JOIN reviews r ON r.movie_id = m.id
  JOIN critics c ON c.critics_id = r.critics_id