MYSQL Left Join COUNTS from multiple tables

Question

I want to add columns that represent counts from other tables.

I have 3 tables.

Messages

MessageID    User      Message      Topic 1            Tom       Hi           ball 2            John      Hey          book 3            Mike      Sup          book 4            Mike      Ok           book 

Topics

Topic      Title     Category1    Category2 ball       Sports    Action       Hot book       School    Study        Hot 

Stars_Given

starID     Topic 1          ball 2          book 3          book 4          book 

I want to end up with:

Topic_Review

Topic    Title     StarCount    UserCount    MessageCount ball     Sports    1            1            1 book     school    3            2            3 

So basically I want to attach 3 columns with counts of unique values (number of stars given within each topic, unique users who have messages within topic, and the number of unique messages in each topic).

I want to eventually be able to filter on the categories (look in both columns) as well.

Also, I want to eventually sort by the counts that I join. Example, I'm going to have a button that sorts by "number of stars" by ascending order, or sort by "number of users" by descending order, etc.

I've tried adapting other people's answers and I can't get it to work properly.

Answer 1

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

Sql Fiddle

Or, you can perform the aggregation in sub-queries, which will likely be more efficient if you have a substantial amount of data in the tables:

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 

Sql Fiddle