CODEWITHSUNDEEP
mysqlsql
Satyam Suman
Satyam Suman

Reputation: 77

SQL query for movie database

Top twenty rated movies (Condition: The movie should be rated/viewed by at least 40 users)? This is my query but It is not giving right result. This database contains 3 tables:

  `select Title 
   from Movie m 
   JOIN
   (select MovieID, Rating 
   from Ratings 
   order by
   Rating)
   as r on 
   m.MovieID = r.MovieID
   limit 20;`

Can you suggest the right query? This image contains the description of tables. It has 3 tables: Movie, Ratings, Users

Upvotes: 1

Views: 2548

Answers (2)

nazmul.3026
nazmul.3026

Reputation: 998

IN SQL

select Title from Movie m  
inner join (select top 20 MovieID,sum(Rating) Rate from Ratings group by movieid having count(UserID)>39 order by sum(Rating) DESC) tbl
on m.MovieID=tbl.MovieID
order by tbl.Rate desc

IN MYSQL

 select Title from Movie m  
    inner join (select  MovieID,sum(Rating) Rate from Ratings group by movieid having count(UserID)>39 order by sum(Rating) DESC limit 20) tbl
    on m.MovieID=tbl.MovieID
    order by tbl.Rate desc

Upvotes: 2

cdaiga
cdaiga

Reputation: 4939

The SQL ORDER BY Keyword. The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword. For more see SQL ORDER BY Keyword - W3Schools Doing this and getting the first 20 would meaning getting the top 20 ranking movies.

Another thing is you want to get the top 20 movies, so you could just get them from the Rating table before doing the joins. This will reduce the size of the data set before the join, hence optimizing the query.

With those in mind you query should be like:

select Title from Movie m JOIN 
(select MovieID, Rating from Ratings order by Rating DESC limit 20) as r
on m.MovieID = r.MovieID;

Upvotes: 0

Related Questions