I have four tables.
posts
| id | title |
+---------+-----------+
| 1 | hello |
| 2 | goodbye |
+---------+-----------+
posts_tags
| tag_id | post_id |
+---------+-----------+
| 1 | 1 |
| 2 | 1 |
| 2 | 2 |
+---------+-----------+
comments
| id | post_id | comment |
+---------+------------+-----------+
| 1 | 1 | hey |
| 2 | 2 | what up |
| 3 | 2 | blah |
+---------+------------+-----------+
tags
| id | name |
+---------+-----------+
| 1 | news |
| 2 | photos |
+---------+-----------+
I want to be able to select the posts, but have this as a result
post.id post.title tags comments
-----------------------------------------------------
1 hello news,photos 1
2 goodbye photos 2
Something like
SELECT *,
GROUP_CONCAT(tags.name) AS tags,
COUNT(comments.id) AS comments
FROM posts
LEFT JOIN comments
ON posts.id = comments.post_id
LEFT JOIN posts_tags
ON posts.id = posts_tags.post_id
LEFT JOIN tags
ON posts_tags.tag_id = tags.id
GROUP BY posts.id
The problem I'm running into is it's not returning the proper count of comments. Instead, it seems to be returning the number of tags. Please advise, thanks for your time :)