I have an ugly query, horrible table names courtesy of MySQL Workbench, and a severe lack of planning on my part to change to less verbose names prior to actually writing the code. Try to move past all of that!

The Problem

I have multiple many-to-many relationships for publications. I'm trying to pull publications by their tag name (category) or by the author's first and last name. I originally was pulling exclusively by tag name, but was requested to search by author as well. When it was solely by tags everything worked flawlessly. I've joined the tables required to search by name and everything has gone haywire. I can sort by tags still, but it returns duplicates. Searching by name gives results, but they aren't limited by name as expected.

The Query

SELECT publicationsId, title, summary, image, publicationDate, citation, publications.dateAdded, firstName, lastName, employee_employeeId,
GROUP_CONCAT(DISTINCT publicationType) AS publicationType                               
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
LEFT JOIN employee ON employee_has_publications.employee_employeeId = employee_employeeId
WHERE firstName LIKE :firstName AND lastName LIKE :lastName
GROUP BY publicationsId, title, summary, image, publicationDate, citation, publications.dateAdded, employee_employeeId, firstName, lastName
HAVING tagName LIKE :tagName
ORDER BY publicationDate DESC, publications.dateAdded DESC 
LIMIT :limit


:tagName, :firstName, and :lastName are '%%' if not searching by name or tag.

Table Structure

enter image description here

  • Any chance you could edit to include your table structures, sample data and then the final desired result? Or even better create a sqlfiddle.com with the details.
    – Taryn
    May 12, 2014 at 19:29
  • @bluefeet I added a limited ERD. The desired result is just the columns in the select statement, but only if the tag name or author name match. If the tag or author name isn't present it should list all publications. May 12, 2014 at 19:36
  • If publication must have at least a publicationType, a employee and a publicationTag, as for the ERD all the LEFT JOIN can be changed to INNER JOIN, and the tagName condition can be moved to the WHERE. A sqlfiddle with some data that results in unexpected rows will help the community to diagnose your problem
    – Serpiton
    May 12, 2014 at 22:13


Your Answer

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

Browse other questions tagged or ask your own question.