Mysql count with two joins

select
  t.Topic,
  t.Title,
  s.StarCount,
  m.UserCount,
  m.MessageCount
from
  Topics t
  left join (
    select 
      Topic, 
      count(distinct User) as UserCount,
      count(*) as MessageCount
    from Messages
    group by Topic
  ) m ON m.Topic = t.Topic
  left join (
    select
      Topic, 
      count(*) as StarCount
    from Stars_Given 
    group by Topic
  ) s ON s.Topic = t.Topic

Source: stackoverflow.com

Similar codes

3 votes258 views1 answer
Get all elements sql
SQL
0 vote278 views1 answer
Implizite joins sql
SQL
1 vote276 views1 answer
Delete row mssql
SQL
0 vote282 views1 answer
Limiting query result using where in sql
SQL
0 vote278 views1 answer
Original Query
SQL
13 votes288 views6 answers
Restart postgresql
SQL
0 vote284 views2 answers
Sql join on wildcard
SQL
0 vote303 views1 answer
How to select only a certain date sql
SQL
0 vote275 views2 answers
Database owner can't create new tables postgres
SQL
0 vote281 views1 answer
Convert rows into columns in oracle
SQL