Retrieving data from three tables using joins

I have the following tables:

Post (开发者_如何学Pythonid, title, author_id) etc
Tags
Post_tags
Author

The following sql query only seems to get the first thread, along with all the tags.

SELECT post. * , author.username,
GROUP_CONCAT( DISTINCT tag.name ORDER BY tag.name DESC SEPARATOR ',' ) AS tags
FROM author, post
JOIN post_tags ON post.id = post_tags.thread_id
JOIN tag ON post_tags.tag_id = tag.id
WHERE author.id = post.author_id

What am I doing wrong here?

---

You usually use GROUP_CONCAT() with a GROUP BY clause.

Without a GROUP BY, it means it will group ALL rows into one and thus show the group concatenation of ALL tags.

The first thread data that you see is a byproduct of MyQSL ill-behaviour that allows you to show fields in the SELECT that are not dependent on the grouping fields (none in your case).

Try your query adding GROUP BY post.id at the end.

SELECT post. *
     , author.username
     , GROUP_CONCAT( DISTINCT tag.name ORDER BY tag.name DESC SEPARATOR ',' )
         AS tags

FROM author
  JOIN post
    ON author.id = post.author_id
  JOIN post_tags
    ON post.id = post_tags.thread_id
  JOIN tag
    ON post_tags.tag_id = tag.id

GROUP BY post.id