my query seems correct, but bring no result

Question

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?

Answer 1

Select r.name, m.title From Rating rg
Left join Reviewer r ON rg.rID = r.rID
Left join Movie m ON rg.mID = m.mID
Where rg.stars > (
    Select Top 1 g.stars From Rating g
    Where -- g.ratingDate != rg.ratingDate AND -- discard same rating
     g.mID = rg.mID and g.rID = rg.rID
     order by ratingDate desc
    )

Answer 2

Try this:

SELECT  r.name,
        m.title    
FROM (  SELECT rid,mid -- Here we get all rid and mid that have been reviewed twice
        FROM rating
        GROUP BY rid,mid
        HAVING COUNT(stars) = 2
        ) AS m 
INNER JOIN (SELECT distinct r1.rid, r1.mid --Here we get all rid and mid that 
            FROM rating r1                 --was rated higher the second time
            INNER JOIN rating r2 
                ON r1.rid = r2.rid AND 
                r1.mid=r2.mid AND
                r1.ratingdate > r2.ratingdate AND
                r1.stars > r2.stars
            ) AS c 
    ON c.rid = m.rid AND 
    c.mid=m.mid
LEFT JOIN Reviewer r 
    ON c.rid=r.rid
LEFT JOIN Movie m 
    ON m.mid=c.mid