I have tried a SELECT query with several possible combinations and types of joins but so far the # of results that show are considerably less than what I was expecting. I am pretty green at this so I know I must be doing something wrong.
I have a comic book database with several tables including a few "lookup" tables due to some many-many relationships.
Table #1 comic:
comicID (unique key)
comicImage (link to Jpeg file)
publisherID (foreign key to publisher table)
titleID (foreign key to title table)
Table #2 publihser
publisherID (unique key)
Table #3 title
titleID (unique key)
Table #4 writer
Table #5 luwriter (lookup table for writer)
writerID (key from writer table)
comicID (key from comic table)
My comic table has close to 7000 records. Each comic has a publisher, title, volume number, issue number, comic picture and a writer (plus a few other fields store in other tables that will not be part of this particular SELECT query).
I want to display details on every comic that is written by a particular writer's last name. User input would submit the writer's last name to the SELECT query. The query should flow like this:
User types in Turner (as the writer's last name) Table #4 provides the writerID for every writer with a last name of Turner plus the last and first names of the writer(s) writerLastName and writerFirstName. Table #5 (the lookup table) provides the comicID for every iteration of those writers with writerLastName = Turner. For every comicID, Table #1 provides issueNumber, comicImage, publisherID, titleID. Table #2 is JOINed with Table #1 by publisherID, which supplies publisherName. Table #3 is JOINed with Table #1 by titleID, which supplies title.
Finally, this data (not all the data present in the tables) is displayed back to the user:
artistLastName, artistFirstName, publisherName, title, volumeNumber, issueNumber, comicImage
Each query that I have run only displays a fraction of the comics that have been written by that author. Any help on how to construct the SELECT query would be great!