Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 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

Parameters

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

Table Structure

enter image description here

share|improve this question
    
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. –  bluefeet May 12 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. –  Matthew Johnson May 12 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 at 22:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.