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!
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.
SELECT publicationsId, title, summary, image, publicationDate, citation, publications.dateAdded, firstName, lastName, employee_employeeId, GROUP_CONCAT(DISTINCT tagName) AS tagName, 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
'%%' if not searching by name or tag.