order by values from different table

[tail]

Sort result by a different tables values

I'm working on a script that shows games compiled in a MySQL database. I'm trying to sort them by highest rated.

 

The ratings are stored in a different table. The tables are set up as follows:

 

table `info`

| id | user_id | name | desc | category | plays | date_added |

 

table `rating`

| game_id | user_id | rating_value |

 

I think this is possible using a JOIN. I don't know much about JOIN's but it seems that it could be done. The way I'm trying to do it right now is add up the rating values for the specific ID, then divide that by the number of ratings for the specific ID, then sort the result by the highest number.

 

I'm using this query to no avail:

SELECT `id` , `name`

FROM `info`

JOIN `rating` AS `rating`

ORDER BY (

COUNT( rating.game_id ) / SUM( rating.rating_value )

) DESC

 

Any help is much appreciated!

[fenway]

Get the result set working first, then worry about the ordering.

[artacus]

Move the

COUNT( rating.game_id ) / SUM( rating.rating_value )

out of the order by clause and put it in the select clause. It will still be broken but you should be able to figure out what you are doing wrong.

[tail]

Got it!

 

SELECT `id` , `name`

FROM `info`

LEFT JOIN `rating` ON rating.game_id = info.id

GROUP BY info.id

ORDER BY (

AVG( rating.rating_value )

) DESC

 

Thanks for the help! Is there a way I can make it so results with < 10 votes doesn't come up first?

[fenway]

You can use a HAVING clause to remove them entirely, or ORDER with that expression.

[tail]

I got to this point:

SELECT info.id 
     , info.name
  FROM info
LEFT OUTER
  JOIN rating 
    ON rating.game_id = info.id
GROUP 
    BY info.id
HAVING COUNT(rating.rating_value) >= 10
ORDER
    BY AVG(rating.rating_value) DESC

 

However only games with > 10 votes showed in the result. How can I make it so all of the games show up, but order priority goes to games with > 10 votes and an avg rating higher than 3?

[fenway]

Add those two expression as additional ORDER BY "columns".