1

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?

Midgetlegs
  • 35
  • 4
  • Are the relationships between {comics, publishers, persons} all many-to-many? Can a single comic really have multiple publishers? I feel like you may have some modeling redundancy with those three join tables. – Aaron Kent Apr 23 '15 at 17:25
  • Why person has relation to both comic and publisher? – Michas Apr 23 '15 at 17:38

1 Answers1

1

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.

Aaron Kent
  • 609
  • 1
  • 5
  • 11
  • It's not common, but occasionally comics do switch publishers so I created that table for those instances. – Midgetlegs Apr 23 '15 at 18:06
  • Your mapping tables do seem circular, though. You have A->B, B->C, and A->C. They shouldn't all be necessary. You may get duplicates from your joins. Notice that I only used two of your mapping tables in the query above. – Aaron Kent Apr 23 '15 at 18:58
  • Yeah, I'm definitely getting duplicates. I'm really not certain how to properly do a search though. For example, let's say someone wants to find a list of "The Transformers" issues that were published by Marvel, but not IDW, and only the ones that Bob Budiansky worked on. I'd have refer first to the comics table, then to publisher2comic to find narrow down the results to Marvel's issues, then refer to person2comic to get what's being searched for. But it's not working out how I'm trying to do it. – Midgetlegs Apr 24 '15 at 22:16
  • Can you help me understand the data? What is person? Is that the author of the comic? Or is that someone who works for the publisher? – Aaron Kent Apr 24 '15 at 23:45
  • Person is anyone that worked on a comic (author, artists, etc). The reason I have a person2publisher table is to see which publishers people have worked under and vice versa. – Midgetlegs Apr 26 '15 at 17:07