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

Question

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 1

I would start with the self join on rating and then bring in the names:

select distinct rv1.name, rv2.name
from rating r1 join
     rating r2
     on r1.mid = r2.mid join
     reviewer rv1
     on rv1.rid = r1.rid join
     reviewer rv2
     on rv2.rid = r2.rid and rv1.name < rv2.name;

Your query is actually very similar. I think the main issue is the select distinct and the ordering by name instead of id.