Announcement

Announcement Module
Collapse
No announcement yet.

count from two different tables possible?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • count from two different tables possible?

    is it possible to return the count of two different joins, e.g.

    select name, count(posts.id) num_posts, count(friends.id) num_friendsfrom users inner join posts on posts.user_id = users.id inner join friends on friends.user_id = users.idgroup by users.id


    when I try this, I get very large numbers for num_posts and num_friends, and the two numbers are identical. if I only try to select one of the counts, then i get the expected number.

    thanks,
    jeff

  • #2
    you might try to doing the counts prior to doing the joins.

    for instance:


    select nameleft join ( select count(1), user_id from posts group by user_id ) p on u.id = p.user_idleft join ( select count(1), user_id from friends group by user_id ) f on u.id = f.user_idfrom users u

    Comment


    • #3
      fantastic... looks like that does it. thanks!

      Comment

      Working...
      X