therealwesfoster Posted August 18, 2008 Share Posted August 18, 2008 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted August 18, 2008 Share Posted August 18, 2008 SELECT i.pic_id, COUNT(*) as comcount FROM images i JOIN comments c ON i.pic_id = c.com_picid ORDER BY comcount DESC Quote Link to comment Share on other sites More sharing options...
therealwesfoster Posted August 18, 2008 Author Share Posted August 18, 2008 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 Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted August 18, 2008 Share Posted August 18, 2008 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 Quote Link to comment Share on other sites More sharing options...
therealwesfoster Posted August 18, 2008 Author Share Posted August 18, 2008 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.