Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2009
    Posts
    1

    Cool Unanswered: MySQL five tables INNER JOIN

    Hello Everybody!

    After spent two days on it, at this point I decided to admit that it is driving me crazy and look for some help!

    I have to obtain the information about employees who can only travel to some specific city after taking some vaccine before flying. The idea is, given certain city, the database should tell me who I can send there to attend our customers?

    The problem involve 5 tables, and after googling around, I notice that there is not much good examples on internet.

    The version 01.1 works fine (the result is correct), but only if the city in question requires no more than one vaccine. If the city requires 2 or more, it craches.

    Therefore, I decided to code the version 01.2. The problem, is that the results are wrong. The correct result given my data should be the same result shown on first table.

    Here is the code:

    *** Version 01.1


    // tables: [city]1----n[requiredvaccine]n----1[vaccine]1----n[takenvaccine]n----1[traveler]

    SELECT traveler.travelerFirstName from
    (traveler LEFT JOIN takenvaccine ON traveler.travelerID = takenvaccine.travelerID)
    LEFT JOIN vaccine ON takenvaccine.vaccineID=vaccine.vaccineID
    WHERE vaccine.vaccineID =
    (SELECT vaccine.vaccineID from
    (city LEFT JOIN requiredvaccine ON city.cityID = requiredvaccine.cityID)
    LEFT JOIN vaccine ON requiredvaccine.vaccineID=vaccine.vaccineID
    WHERE city.cityName = 'Boston'
    );

    +---------------------+
    | travelerFirstName |
    +---------------------+
    | Paul |
    | Jack |
    | Bill |
    | Leonidas |
    +---------------------+

    *** Version 01.2

    SELECT traveler.travelerFirstName from
    (traveler INNER JOIN takenvaccine ON traveler.travelerID = takenvaccine.travelerID)
    INNER JOIN vaccine ON takenvaccine.vaccineID=vaccine.vaccineID
    INNER JOIN requiredvaccine ON vaccine.vaccineID=requiredvaccine.requiredvaccineI D
    INNER JOIN city ON requiredvaccine.cityID = city.cityID
    WHERE city.cityName = 'Boston';

    +---------------------+
    | travelerFirstName |
    +---------------------+
    | Mark |
    | Jack |
    | Leonidas |
    +---------------------+

    So, why and what is wrong here? please any hints would be greatly appreciated!!!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if a city requires more than one vaccine, then the condition your search needs to satisfy is that the traveller has all of them

    the easiest way to do this is to count all the vaccines that the traveller has for that particular city, and compare that count to the total count of vaccines that the same city needs
    Code:
    SELECT traveler.travelerFirstName 
      FROM city
    INNER
      JOIN requiredvaccine
        ON requiredvaccine.cityID = city.cityID
    INNER
      JOIN takenvaccine
        ON takenvaccine.vaccineID = requiredvaccine.requiredvaccineID
    INNER
      JOIN traveler
        ON traveler.travelerID = takenvaccine.travelerID
    GROUP
        BY traveler.travelerFirstName 
    HAVING COUNT(*) =
           ( SELECT COUNT(*)
               FROM city
             INNER
               JOIN requiredvaccine
                 ON requiredvaccine.cityID = city.cityID
              WHERE city.cityName = 'Boston' )
     WHERE city.cityName = 'Boston';
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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