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?