For all pairs of reviewers such that both reviewers gave a rating to the same movie, return the names of both reviewers

Stanford Self Paced Course for SQL question:

For all pairs of reviewers such that both reviewers gave a rating to the same movie, return the names of both reviewers. Eliminate duplicates, don’t pair reviewers with themselves, and include each pair only once. For each pair, return the names in the pair in alphabetical order.

The schema :

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.

My attempt:

Select R.Name, R2.Name 
From Reviewer R 
    Join Reviewer R2 on (R.rID = R2.rID)
    Join Rating Rt on (Rt.rID = R2.rID) 
    Join Rating Rt2 on (Rt2.rID = R.rID)
 Where Rt.MID = Rt2.mID and R.rID < r2.rID

I know I need to have a table with 2 Reviewer Name columns and 2 Movie columns. I apply the condition so that the movies have to equal each other and the condition that the Id’s cannot be the same as the question says “Don’t pair reviewers with themselves, and include each pair only once“.

My result is empty (incorrect). What am I doing wrong?

Answer

You must join the table rating twice to the table movie and for each join, join the table reviewer.
Then filter the result so that reviewers are not paired with themselves and by using distinct and min() and max() functions make sure that each pair is not repeated:

select distinct
  min(v1.name, v2.name) reviewer1,
  max(v1.name, v2.name) reviewer2
from movie m
inner join rating r1 on r1.mid = m.mid
inner join rating r2 on r2.mid = m.mid
inner join reviewer v1 on v1.rid = r1.rid
inner join reviewer v2 on v2.rid = r2.rid
where v1.rid <> v2.rid
order by reviewer1, reviewer2