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?