Sort by rating contained in another table?

[listenmirndt]

I have one table called 'PAGES' and another called 'ratings'.

I want to sort the results of 'PAGES' by how well it is rated in the 'ratings' table.

The 'PAGES' table has an ID field which is auto-incrementing, and the 'ratings' table has an ID field that contains the ID number of the PAGES table.

Finally, the ratings table has a field called 'total_value' which contains the rating score.

So I currently have,

$SQL = "SELECT * from PAGES ORDER BY ID DESC LIMIT 10";

how do I code this?

thanks
-Arron

[johanafm]

You use (left) INNER JOIN or LEFT (outer) JOIN to join the tables together. The difference is that an inner join only includes rows from pages that have at least one corresponding row in ratings, while an outer join will provide null values for ratings' fields when a row in pages have no corresponding row in ratings.

SELECT pages.field
FROM pages
LEFT JOIN ratings ON pages.id = ratings.page_id
ORDER BY total_value DESC

This query would include rows in pages that have no row in ratings, but since it provides null values for total_value in this case, they will sort below pages with a rating.