Forum: Ruby on Rails somewhat advanced question about mysql

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
elisfanclub (Guest)
on 2006-05-13 22:19
(Received via mailing list)
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?
This topic is locked and can not be replied to.