php - 查询以在类别中链接时显示所有博客文章

Question

所以我有一个查询，应该从表中获取所有帖子，同时链接帖子类别和创建帖子的用户。我收到的只是返回的一个帖子，而不是所有帖子。下面是架构:

Posts
=====
id

Categories
==========
id

Post categories
===============
postID
categoryID

这是我到目前为止的 SQL 代码，有点深，但它将所有类别连接到一个字段中。

SELECT
    blgpostcategories.*,
    blgcategories.id,
    GROUP_CONCAT(blgcategories.name) AS categories,
    blgposts.*,
    users.firstName,
    users.id AS usersId,
    users.lastName,
    users.email
FROM blgposts
RIGHT OUTER JOIN blgpostcategories
    ON blgposts.id = blgpostcategories.postID
RIGHT OUTER JOIN blgcategories
    ON blgpostcategories.categoryID = blgcategories.id
INNER JOIN users
    ON blgposts.userID = users.id

来自 JNK 的更新查询 - 仍然只返回一行:-(

SELECT
  blgpostcategories.*,
  blgcategories.id,
  GROUP_CONCAT(blgcategories.name) AS categories,
  blgposts.*
FROM blgposts

LEFT OUTER JOIN blgpostcategories
  ON blgposts.id = blgpostcategories.postID
LEFT OUTER JOIN blgcategories
  ON blgpostcategories.categoryID = blgcategories.id

Answer 1

在answer中亚当·罗宾逊(Adam Robinson)对类似问题的回答

Because you're using an aggregate in your query (GROUP_CONCAT), your query is being grouped. Since you have no group by clause, your group is the entire result set (hence seeing every tag the author has used). Because MySQL allows for using non-grouped columns in grouped statements, you aren't getting an error, but you aren't getting the query that you want.

In order to retrieve the proper results, you need to group your query on thread.id.

在您的情况下，只需添加 GROUP BY blgcategories.id 就可以了