SQL query questions

Discussion in 'Web Design and Development' started by raymondu999, Mar 28, 2009.

  1. raymondu999 macrumors 65816

    Joined:
    Feb 11, 2008
    #1
    Hey all... need a bit of help here... I have a database, and I'll just describe the relevant parts...

    The "movie" table has the pk of "movie_id," and has attribute "title"

    The "movie_rating" table has no pk, instead having two fk's, one "movie_id" and "user_id," each referencing the movie and user databases. It also has one attribute, "rating."

    Now, I must write an SQL query in such a way that it returns the "movie" records with the top 20 highest ratings. There's nowhere in the database that stores the average rating of each movie. Hence the SQL query must look through the rating table and calculate the average for each movie. Then if there are 2 movies with the same rating it takes the movie with more votes as higher.

    I was thinking SELECT Max(avg(movie_rating)), WHERE movie_id = whatever?

    Also, there's another problem I'm having. I am quite stumped as to how go about doing this. For this same database, there is another table, where "artists" are stored. Then there are 3 associative tables, respectively, act, write, and direct. These all have 2 fk's each, movie_id and artist_id. Now I need to print out all the movies that each artist has starred in, and also print out the average rating (from the tables aforementioned) for each movie.

    note: nvm about the PHP displaying part, I'm just sorting out the SQL side first.

    I was thinking it'll prolly be something along those lines, but I can't think of where to go from there. Anyone can lend a hand, or a pointing finger?

    Thanks in advance for any help! :eek:
     
  2. belvdr macrumors 601

    Joined:
    Aug 15, 2005
    #2
    SELECT Max(avg(movie_rating)), WHERE movie_id = whatever

    Close, (and assuming an Oracle database) but what you need to do is:

    Code:
    SELECT mo.title as title, AVG(mr.rating) as avg_rating
    FROM movie mo, movie_rating mr
    WHERE mo.movie_id = mr.movie_id
    GROUP BY mo.title
    ORDER BY avg_rating desc
    LIMIT 20;
    
    This should sort the data with the highest rating first.

    This really depends on the size of the database. If the database is large, I would create a materialized view and refresh it when needed. If the database is small, a view would work just fine.
     
  3. raymondu999 thread starter macrumors 65816

    Joined:
    Feb 11, 2008
    #3
    I'm actually running on MySQL. And maybe this wasn't clear in the first post, but I don't mean "top 20" as in records 1-20, but for example, say there are 400 records. I have to work out the 20 with the most votes, and highest ranking of votes.
     
  4. belvdr macrumors 601

    Joined:
    Aug 15, 2005
    #4
    That's what that does. I'll fix it for MySQL.

    EDIT: Corrected for MySQL. It calculates the title and the average rating for that title. It then sorts by the average rating, and then limits the number of rows to 20.
     
  5. raymondu999 thread starter macrumors 65816

    Joined:
    Feb 11, 2008
    #5
    woah. Thanks a bunch mate. Now it all clears up. The rownum < 21 was the one throwing me off. Any ideas on my second part though?
     
  6. belvdr macrumors 601

    Joined:
    Aug 15, 2005
    #6
    Code:
    SELECT ar.artist_name, mo.title as title, AVG(mr.rating) as avg_rating
    FROM movie mo, movie_rating mr, artists ar, act
    WHERE mo.movie_id = mr.movie_id
    AND mo.movie_id = ar.movie_id
    AND ar.artist_id = act.artist_id
    AND ar.artist_name = 'Clint Eastwood'
    GROUP BY ar.artist_name, mo.title
    ORDER BY avg_rating desc;
    
    I believe that will do it.
     
  7. raymondu999 thread starter macrumors 65816

    Joined:
    Feb 11, 2008
    #7
    Hi there. Thanks for the tip, the first part worked wonders! However, for the second part I think I might've miscommunicated what I meant. I meant that I have these tables (sql is attached so that you can see how the db is structured).

    Now, I have to take, for each celebrity, the movies that he/she starred in, the year of release of the movie, the "average rating" as from the query above, and the amount of awards. So for example, an artist Mr. Bob Smith might have the following returned for him: (attached)

    I'm really stumped on this:confused: any help is appreciated:(
     

    Attached Files:

  8. raymondu999 thread starter macrumors 65816

    Joined:
    Feb 11, 2008
  9. belvdr macrumors 601

    Joined:
    Aug 15, 2005
    #9
    Code:
    SELECT AVG(mr.rating) as avg_rating, mo.title as title, mo.release_date as Year, count(aa.artist_id) as Num_Awards
    FROM movie mo, movie_rating mr, artists ar, cast_movie cm, artist_award aa
    WHERE mo.movie_id = mr.movie_id
    AND mo.movie_id = ar.movie_id
    AND ar.artist_id = cm.artist_id
    AND ar.artist_id = aa.artist_id
    AND mo.movie_id = aa.movie_id
    AND ar.artist_name = 'Clint Eastwood'
    GROUP BY mo.title, mo.release_date
    ORDER BY avg_rating asc;
    
     

Share This Page