SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Mar 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with joining multiple tables including one lookup table

    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,398
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    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. #3
    SitePoint Member
    Join Date
    Mar 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    695
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
      left
      join writer
        on luwriternew.writerID = writer.writerID
       and writer.writerLastName = 'turner'

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,398
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    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):
    Code:
    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. #6
    SitePoint Member
    Join Date
    Mar 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •