my query seems correct, but bring no result

Viewed 92

I have database:

Movie ( mID, title, year, director ) : There is a movie with ID number mID, a title, a release year, and a director.

Reviewer ( rID, name) : The reviewer with ID number rID has a certain name.

Rating ( rID, mID, stars, ratingDate ) : The reviewer rID gave the movie mID a number of stars rating (1-5) on a certain ratingDate.

I need 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.

my query

select r.name, m.title    
from Reviewer r, Movie m, Rating rg    
where rg.stars=(select max(g.stars) from Rating g    
            where rg.mID=g.mid and g.rID=rg.rID    
            group by g.stars    
            having count(g.mid)=2)    

And it brings me no result Could you please tell me where is my mistake?

2 Answers
Related