How can I join these two queries?

Question

Here's what I want to accomplish: Select all rows from table "posts" while also fetching linked entities, such as the author (from table "users") and the names of the categories the post belongs to (from table "categories").

These are my two queries so far:
This one fetches the posts:

SELECT
posts.*
, users.authorName AS author
FROM posts
INNER JOIN users
ON users.id = posts.author

And this one fetches a comma separated list of categories for a specific post:

SELECT
GROUP_CONCAT(categories.category) AS categories
FROM categories
INNER JOIN post_category
ON post_category.categoryID = categories.id
WHERE
post_category.postID = ?

The two queries on their own work fine.

Naturally when the two are combined, I wouldn't need the WHERE clause of the second query. I tried using the second query as a sub-query within the first one's SELECT clause, but that fetched a comma separated list of ALL categories for ALL posts. I want only the categories for the post I'm currently iterating over.

Any help would be greatly appreciated, and I apologize if any of this is unclear - it's hard enough for me to think about, let alone describe to other people ;)

Answer 1

Something like this:

SELECT posts.*, users.authorName AS author,
       GROUP_CONCAT(categories.category) AS categories
  FROM posts, users, categories, post_category
 WHERE users.id = posts.author
   AND post_category.categoryID = categories.id
   AND post_category.postID = posts.id
 GROUP BY posts.* /* list actual columns here */, author

Or did I miss something?

Answer 2

I think your subquery approach should work fine, but you still need the where clause in your subquery. How else would MySQL know which rows to retrieve? Try:

SELECT posts.*, users.authorName AS author,
    (SELECT GROUP_CONCAT(categories.category) 
     FROM categories
     INNER JOIN post_category ON post_category.categoryID = categories.id
     WHERE post_category.postID = posts.postID) AS categories
FROM posts
INNER JOIN users ON users.id = posts.author