MySQL Forums
Forum List  »  Newbie

Help with a Left Join
Posted by: Brandon Rodriguez
Date: August 15, 2004 12:25AM

I'm having some trouble with a mySQL query so I'd thought I'd give it a shot here... It does require some background information as follows:

I'm creating a script in PHP that allows users to upload files and subsequently have people rate the files. I have two tables in the database:

games_files - holds all the information about the files that have been uploaded
games_ratings - holds the ratings for the files, has it's own unique id column as well as a "trackid" column that holds the games_files.id value the rating is for...

The games_ratings table has a column, "rating", in which a numeric value exists for the rating. If the rating is just a comment (with no actual rating), the row will have a 0 there.

Now with all that, I'm trying to print a list of the last 10 files to have been submitted along with their current rating, and came up with the following query:

SELECT games_files.id, games_files.trackname, AVG(games_ratings.rating) AS average_rating
FROM games_files
LEFT JOIN games_ratings ON games_files.id=games_ratings.trackid AND games_ratings.rating<>'0' AND games_files.type = '4'
GROUP BY games_files.id
ORDER BY games_files.id DESC LIMIT 10

Now technically the query works, but the page it's on now takes an abnormally time to load (before I edited the query to that, it was almost instantaneous). Is the mySQL query inefficient, or is there a better one? I originally just used a "WHERE games_files.id=games_ratings.trackid" instead of the left join, but that only returned file listings with a rating already existing (files with no rating yet were not displayed). Help? Thanks!

Options: ReplyQuote


Subject
Written By
Posted
Help with a Left Join
August 15, 2004 12:25AM
August 15, 2004 03:01AM
August 15, 2004 02:50PM
August 15, 2004 03:31PM
August 15, 2004 03:38PM
August 16, 2004 02:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.