JOIN help

[moon 111]

Would something like this work? (there is also a row rating.)

 

SELECT * FROM 
site AS s, rating AS r 
WHERE s.id = r.sid ORDER BY AVG(r.rating) DESC

[moon 111]

I'll be more specific:

 

I have a table 'site' that has information such as id, title, url etc. I have another table 'rating' which has the cols sid, uid, rating. sid reffers to the site that was rated. How can I order the information in the table 'site' by the average rating of all the rows where the sid (in rating) equals id (in site)?

[aschk]

So, to break it down. You need:

The average rating for each site, and then have the sites ordered by their average rating

 

SELECT s.id as 'site_id'
      ,AVG(r.rating) as 'average_rating'
FROM site s
JOIN rating r ON s.id = r.sid
GROUP BY s.id
ORDER BY average_rating DESC

[fenway]

This is very clearly a double-post -- topic locked.