[SOLVED] Query Help (again)

[therealwesfoster]

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 tables are setup as follows, I'm only listing the table column names that are relevant:

 

comments

com_id (the ID of the comment)

com_picid (the ID of the pic it is related to)

 

images

pic_id (the ID of the pic)

 

Please help Thanks

 

Wes

[Barand]

SELECT i.pic_id, COUNT(*) as comcount

FROM images i

JOIN comments c ON i.pic_id = c.com_picid

ORDER BY comcount DESC

[therealwesfoster]

Thanks barand, but here's what I got:

 

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

 

Wes

[cooldude832]

try

SELECT i.pic_id, COUNT(*) as comcount
FROM images i
JOIN comments c ON i.pic_id = c.com_picid
GROUP BY i.pic_id
ORDER BY comcount DESC

[therealwesfoster]

try

SELECT i.pic_id, COUNT(*) as comcount
FROM images i
JOIN comments c ON i.pic_id = c.com_picid
GROUP BY i.pic_id
ORDER BY comcount DESC

 

Awesome, thanks guys!

 

Wes