MYSQL：用union三表选择count

Question

I have three table content information about posts

and each table has Post_id it's foreign_key for Post table

first table = `likes`  
second table = `Comment`  
and last one = `Visitor` 

each Table has some info about users like session or id and etc

i need to create new view table contain post id and the number of visitor , likes , comment

i tried this

SELECT *


  from (

 select id   , count(id) as Comment  
from Post left join Comment  on  id = Post_id 
group by id

  UNION

select id, count(id) as Visitor    
from Post left join Visitor  on id = Post_id 
group by id
UNION

select id, count(id) as Likes  
from Post left join Likes  on id = Post_id 
group by id

) CountsTable
GROUP BY CountsTable.id

but it didnt work . i dont know why the result is only the first inner select

in my example the result is

|    id  | Comment| 
|--------|------- | 
|    1   |   55   |    
|    2   |   25   |   
|    3   |   12   |   

i expect something like that

| id | Comment | Likes | Visitor |
|--------------|-------|---------|
|  1 |   55    |  100  |   2000  |    

Answer 1

No need to use UNION. Count the records for each post in all three tables and Left Join result with Post table

Try something like this

SELECT id,
        comments,
        vistors,
        likes
FROM   Post p
        LEFT JOIN (SELECT Count(Post_id) comments, Post_id
                    FROM   Comment
                    GROUP  BY Post_id) c
                ON p.id = c.Post_id
        LEFT JOIN (SELECT Count(Post_id) vistors, Post_id
                    FROM   Visitor
                    GROUP  BY Post_id) v
                ON p.id = v.Post_id
        LEFT JOIN (SELECT Count(Post_id) likes, Post_id
                    FROM   Likes
                    GROUP  BY Post_id) l
                ON p.id = l.Post_id