Can you use only single select to write this query?-CodePudding

Question: Find the titles of movies that have been reviewed by at least two reviewers. Please write an SQL with only one SELECT and without GROUP BY.

here is a query with more than one select:

select title,"count(M.mID)" from (select M.title,count(M.mID) from movie M,rating R where M.mID=R.mID GROUP by M.mID) WHERE "count(M.mID)">=2

enter image description here

CodePudding user response：

If there is a column like an ID in the table rating that distinguishes the reviewers, then you can do a self join of rating and join it to movie:

SELECT DISTINCT m.title
FROM movie m
INNER JOIN rating r1 ON r1.mID = m.mID
INNER JOIN rating r2 ON r2.mID = m.mID AND r2.ID <> r1.ID;

The 2nd join of rating will filter out the movies for which there is only 1 reviewer.