开发者

Need help on a JOIN query that almost works

开发者 https://www.devze.com 2022-12-20 01:44 出处:网络
I have four tables. posts id| title| +---------+------开发者_Go百科-----+ 1| hello| 2| goodbye|

I have four tables.

posts
| id      | title     |
+---------+------开发者_Go百科-----+
| 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 :)


try COUNT(distinct comments.id)

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号