Search code examples
mysqljunction-table

Query to Pull Data from 2 Junction Tables

I'm creating a database for comics. Right now I have 3 main tables (comics, publishers, people) and 3 junction tables (person2comic, publisher2comic and person2publisher). I want to be able to have a search form that allows searching by any combination of title, issue number, publisher, and person. When referencing only one junction table, I use a variation of this depending on what's being searched for: 

SELECT comicTitle, comicIssue, firstName, lastName 
FROM person2comic 
JOIN comics ON comics.comicID = person2comic.comicID 
AND comics.comictitle LIKE "%walk%" ;

If someone were to search by title, publisher and person, I'm not sure how to set up the statement since it would require using two of the junction tables. Would it be a nested query situation or something else?

Solution

  • You can have arbitrarily many joins. Not exactly sure on all of your column names, but this should roughly work:

    SELECT * 
FROM people
JOIN person2comic p2c ON people.id = ptc.person
JOIN comic ON p2c.comic = comic.id
JOIN publisher2comic pub2c ON comic.id = pub2c.comic
JOIN publisher ON pub2c.publisher = publisher.id

    Also note that your schema may be inefficient if you relationships all aren't many-to-many. See my comment.