MySQL只返回1行数据库中的多个条目

Question

我正在为员工创建一个所有出版物的列表，并且我目前正在执行一个查询。它运行时没有语法错误，但它只返回一行。有关表格的ERD如下：

在出版物与员工、出版物发布标签以及出版物和出版物类型之间存在着多到多的关系.每个出版物必须至少有一个标签和一个类型。

查询

SELECT publicationsId, title, summary, image, publicationDate, citation, dateAdded, GROUP_CONCAT(DISTINCT tagName) AS tagName, GROUP_CONCAT(DISTINCT publicationType) AS publicationType, employee_employeeId
FROM publications
LEFT JOIN publications_have_publicationTags ON publications_have_publicationTags.publications_publicationsId = publications.publicationsId
LEFT JOIN publicationTags ON publications_have_publicationTags.publicationTags_publicationTagId = publicationTags.publicationTagId
LEFT JOIN publications_have_publicationTypes ON publications.publicationsId = publications_have_publicationTypes.publications_publicationsId
LEFT JOIN publicationTypes ON publications_have_publicationTypes.publicationTypes_publicationTypeId = publicationTypes.publicationTypeId
LEFT JOIN employee_has_publications ON publications.publicationsId = employee_has_publications.publications_publicationsId
ORDER BY dateAdded DESC

额外信息

我最初使用的是同样效果的INNER JOIN。我在网站上使用PDO，但在phpmyadmin中运行查询时得到了相同的结果。

如何在一个查询中获取出版物信息及其相关标记和类型？或者我应该运行单独的查询来消除复杂性？

Answer 1

您的select语句包括group_concat()。这是一个聚合函数。因为没有group by，这意味着所有行都被视为一个组，因此返回一行。

也许你的意思是：

SELECT publicationsId, title, summary, image, publicationDate, citation, dateAdded,
       GROUP_CONCAT(DISTINCT tagName) AS tagName,
       GROUP_CONCAT(DISTINCT publicationType) AS publicationType,
       employee_employeeId
FROM publications
LEFT JOIN publications_have_publicationTags ON publications_have_publicationTags.publications_publicationsId = publications.publicationsId
LEFT JOIN publicationTags ON publications_have_publicationTags.publicationTags_publicationTagId = publicationTags.publicationTagId
LEFT JOIN publications_have_publicationTypes ON publications.publicationsId = publications_have_publicationTypes.publications_publicationsId
LEFT JOIN publicationTypes ON publications_have_publicationTypes.publicationTypes_publicationTypeId = publicationTypes.publicationTypeId
LEFT JOIN employee_has_publications ON publications.publicationsId = employee_has_publications.publications_publicationsId
GROUP BY publicationsId, title, summary, image, publicationDate, citation, dateAdded, emplyee_employeeid
ORDER BY dateAdded DESC;