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 |
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
You can do it with a Left Join query, e.g.:
select u.id, count(distinct l.id) as likes, count(distinct c.id) as comments
from user u left join likes l on u.id = l.user_id
left join comments c on u.id = c.user_id
group by u.id;
Here is SQL Fiddle.
SELECT id, SUM(Comment),SUM(Visitor),SUM(Likes) from (
select id , count(id) as Comment, 0 as Visitor, 0 as Likes
from Post left join Comment on id = Post_id
group by id
UNION ALL
select id, 0 as Comment, count(id) as Visitor , 0 as Likes
from Post left join Visitor on id = Post_id
group by id
UNION ALL
select id, 0 as Comment, 0 as Visitor, count(id) as Likes
from Post left join Likes on id = Post_id
group by id
) CountsTable
GROUP BY CountsTable.id