1

I am creating a list of all publications for employees, and I am currently stuck on a query. It runs with no syntactical errors, but it only returns one row. The ERD for the relevant tables is as follows:

enter image description here

There is a many-to-many relationship between publications and employees, publications publication tags, and publications and publication types. Each publication must have at least one tag and one type.

The Query

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

Extra Info

I was originally using INNER JOIN with the same effect. I'm using PDO on the website, but I get the same result when running the query in phpmyadmin.

How can I get the publications information, and their related tags and types in one query? Or should I just run separate queries to eliminate the complexity?

3
  • 1
    For one row issue you can use GROUP BY employee_employeeId before order by Apr 30, 2014 at 19:51
  • I'm not trying to limit the rows, it's not getting them as is. Apr 30, 2014 at 19:52
  • 1
    My apologies @MKhalidJunaid, it appears that you were correct. I didn't think it would have that effect. Apr 30, 2014 at 19:59

1 Answer 1

3

Your select statement includes group_concat(). This is an aggregation function. Because there is no group by, this means that all the rows are treated as one group, and hence one row is returned.

Perhaps you mean:

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;
1
  • Nailed it, many thanks! There's a minor typo on the GROUP BY line, on employee, but fixed and ran and it's coming up as expected. Apr 30, 2014 at 19:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.