so here is the deal. i've got three tables: lists, items, comments, and this is how they are laid out: lists table id title items table id list_id item comments table id list_id comment to give you some background. in order to get a list of all the lists i am making this call: SELECT lists.id, title, count(items.list_id) AS total_list_items FROM lists INNER JOIN items ON list_id = lists.id GROUP BY list_id ORDER BY created_on DESC LIMIT 10 i need to make that kind of a call because when i print out the list of lists they look like this: 10 favorite movies 15 favorite cheese 8 places to eat sushi etc etc. but i'm getting the number at the beginning from the "total_list_items" in the call from above. now comes the complicated part. i am trying to get a list of the most commented lists, and in that list i need all the information from above, but i also need to count "comments.list_id" and set that as "total_comments" or something. this is what i've come up with: SELECT lists.id, title, count(comments.list_id) AS total_comments, count(items.list_id) AS total_items FROM lists INNER JOIN comments ON comments.list_id = lists.id INNER JOIN items ON items.list_id = lists.id GROUP BY comments.list_id ORDER BY total_comments DESC LIMIT 10 but that doesn't work. 2 of the colums that it returns are "total_comments" and "total_items" but both columns have crazy values that are nothing like what they are by themselves. it's almost like the values of each were multiplied together or something. so, what are your suggestions?
on 2006-05-13 20:19