Help with joining multiple tables including one lookup table

Databases
1

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)
issueNumber
comicImage (link to Jpeg file)
publisherID (foreign key to publisher table)
titleID (foreign key to title table)

Table #2 publihser
publisherID (unique key)
publisherName

Table #3 title
titleID (unique key)
title
volumeNumber

Table #4 writer
writerID
writerLastName
writerFirstName

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!

2

Welcome to the SP forums.

It looks like you have to INNER JOIN all the tables just as you described. Don’t forget the ON clauses.
Why don’t you post your query, so we can take a look at it?

3

Hi Guido!

Thanks for the response. Here is the latest SELECT query I have run, which is only producing a fraction of the number of records I am expecting.

SELECT comic.comicID, comic.issueNumber, comic.comicPicture, publisher.publisherName, title.title, title.volumeNumber,
writer.writerLastName, writer.writerFirstName
FROM comic
LEFT JOIN publisher
ON comic.publisherID = publisher.publisherID
LEFT JOIN title
ON comic.titleID = title.titleID
LEFT JOIN luwriternew
ON comic.comicID = luwriternew.comicID
LEFT JOIN writer
ON luwriternew.writerID = writer.writerID
WHERE writer.writerLastName LIKE turner
ORDER BY title.title, title.volumeNumber, comic.issueNumber

4 

  left
  join writer
    on luwriternew.writerID = writer.writerID
   and writer.writerLastName = 'turner'
5

Since you only want the comics written by a given writer, you can use INNER JOINs instead of LEFT OUTER JOINs, and I would start with the writers table in this case (because the writer’s name is the starting point):


SELECT 
    comic.comicID
  , comic.issueNumber
  , comic.comicPicture
  , publisher.publisherName
  , title.title
  , title.volumeNumber
  , writer.writerLastName
  , writer.writerFirstName
FROM writer
INNER JOIN luwriter
ON writer.writerID = luwriter.writerID
INNER JOIN comic
ON luwriter.comicID = comic.comicID
INNER JOIN publisher
ON comic.publisherID = publisher.publisherID
INNER JOIN title
ON comic.titleID = title.titleID
WHERE writer.writerLastName LIKE turner
ORDER BY title.title, title.volumeNumber, comic.issueNumber

Of course, all comics must have a title and a publisher that exist in those tables, otherwise they won’t be selected. If there are comics without a row in the title or publisher table, you should use LEFT JOINs for those tables.

6

Many thanks, that worked and produced results that include all expected records…whew! My old query must have been limited due to the order of the joins but I am unsure exactly why. I have now adapted the SELECT query to a search by artist as well as one for a search by main character and each worked perfectly. Fortunately all comics have a title and publisher so that wasn’t an issue (no pun intended).

If you don’t mind, in the next day or two, I will post one further challenge, which is to SELECT and retrieve all of the data elements belonging to one comic for display. There are five lookup tables as there are many, many-many relationships possible with a comic book. For instance there may be more than one writer contributing to one comic, in fact there can be a dozen. Same with artists and same with character. I created the database structure some time ago so I have to refresh my rational as to why I built a certain section (relating to variant covers) the way it is. At the moment, it seems overly complicated in some other tables relating to cover variants.

Thanks a huge amount for the assist on this one and I will post the next one once I pause to re-figure the logic.

Hal