[SOLVED] Query Question

[therealwesfoster]

Ok, situation 1: I have 2 tables, 1 is "comments" and the other is "images". Users comment the images etc.

 

Well on one page I'm wanting to sort the images by their number of comments. How would I create an SQL query to do this? The easy way would be to put a "total_comments" field in the "images" table, but I'll do that as a last resort.

 

Situation 2

I'm wanting to sort the images by their rating (which is figured by dividing "pic_rating" / "pic_votes"). How can I do that simple math in an SQL query in order to get the top rated pics to show?

 

 

Thanks

[mbeals]

first you need to count the number of comments per image:

 

Assuming that each comment has a reference back to the image table (imageID)

 

Select imageID, count(imageID) as number from Comments group by imageID

 

Creates a table consisting of the image ID and number of comments with that ID.

 

Now join it to the comments table:

 

Select
images.*,
commentInfo.*
from 
(Select imageID, count(imageID) as number from Comments group by imageID) as commentInfo
join images on commentinfo.imageID = images.index
order by commentInfo.number

 

Sit 2

 

Select pic_rating / pic_votes as rating from images order by rating

 

 

[therealwesfoster]

Wow, good call. It works. Big thanks to you man!

 

Wes