SQL logic needed help possible subquerying-CodePudding

Question from an exercise that I am not able to solve. I do not have enough knowledge.

query = For all cases where the same reviewer rated the same movie twice and gave it a higher rating the second time, return the reviewer's name and the title of the movie

If I try this:

SELECT  title, max (stars)
FROM Movie
  JOIN Rating on Movie.mID = Rating.mID

I get only one movie, I want to be able to see all movies from table "Movie" and the highest rate in case they have more than one rate in table "Rating"

CodePudding user response：

What I get from question I guess this query will solve your problem

SELECT
  mov.`title`,
  MAX(rat.`rating`)
FROM
  rating rat
  JOIN movie mov
    ON rat.`movie_id` = mov.`id`
WHERE mov.`id` IN
  (SELECT
    r.`movie_id`
  FROM
    rating r
  GROUP BY r.`movie_id`
  HAVING COUNT(*) > 1)
GROUP BY mov.`id`

Movie Table

Rating Table

Result

CodePudding user response：

Use LEFT OUTER JOIN, to bring all values of the 'Movie' table. Add Group by Clause to get the Title record only one, ie The Max Stars in case the stars value is null, handle the output. MaxRating alias for the max value

The script eg.

SELECT Movie.title, max(IFNULL(Rating.stars,0)) MaxRating FROM Movie LEFT JOIN Rating on Movie.mID = Rating.mID group by Movie.title