title: Join multiple tables, select counts from different tables and group by one column in one query
body: <p>I need to join multiple tables, select counts from different tables and group by one column in one query.  This is how I would do this separately:</p>
  
  <pre><code>select      c.CommunityName, SUM(case when m.ListKey = c.ListKey then 1 else 0 end) as Posts
  from        Community c with(NOLOCK)
  join        messages_ m with(NOLOCK)
  on          c.ListKey = m.ListKey
  group by    c.CommunityName
  
  select      c.CommunityName, SUM(case when b.CommunityKey = c.CommunityKey then 1 else 0 end) as Blogs
  from        Community c with(NOLOCK)
  join        Blog b with(NOLOCK)
  on          c.CommunityKey = b.CommunityKey
  group by    c.CommunityName
  
  select      c.CommunityName, SUM(case when ce.CommunityKey = c.CommunityKey then 1 else 0 end) as Events
  from        Community c with(NOLOCK)
  join        CalendarEvent ce with(NOLOCK)
  on          c.CommunityKey = ce.CommunityKey
  where       ce.StartDateTime &gt;= GETDATE()
  group by    c.CommunityName
  </code></pre>
  
  <p>or simply</p>
  
  <pre><code>select      c.CommunityName, COUNT(*)
  from        Community c with(NOLOCK)
  join        messages_ m with(NOLOCK)
  on          c.ListKey = m.ListKey
  group by    c.CommunityName
  
  select      c.CommunityName, COUNT(*)
  from        Community c with(NOLOCK)
  join        Blog b with(NOLOCK)
  on          c.CommunityKey = b.CommunityKey
  group by    c.CommunityName
  
  select      c.CommunityName, COUNT(*)
  from        Community c with(NOLOCK)
  join        CalendarEvent ce with(NOLOCK)
  on          c.CommunityKey = ce.CommunityKey
  where       ce.StartDateTime &gt;= GETDATE()
  group by    c.CommunityName
  </code></pre>
  
  <p>There are more tables, some that require additional joins...  Can someone please help?</p>