SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb 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.requiredvaccineID
    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
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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';
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool

    Thanks a lot for the suggestion.

    Now it gives the message:

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
    corresponds to your MySQL server version for the right syntax to use near 'WHERE
    site.siteName = 'Boston'' at line 21

    Any other hints?.......

  4. #4
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    Yahooo!!!

    Now it is working... a little better at least.

    Here is the code... modified version that works for this case:
    +----------+---------------------+--------------+
    | siteName | technicianFirstName | whaName |
    +----------+---------------------+--------------+
    | Boston | Paul | Yellow Fever |
    | Boston | Jack | Yellow Fever |
    | Boston | Bill | Yellow Fever |
    | Boston | Leonidas | Yellow Fever |
    +----------+---------------------+--------------+

    *** Version 01.3
    // this query links the 5 tables corectly
    // --- but it is asking for some specific city, what are the employees who took
    // ------ some of the vaccine required for that city.

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

    The key apparently also is, that the most important query mostly direct linked with your question must be at the beginning
    of the 'SELECT', and also present on your 'WHERE' conditional expression. Nice! right? ... almost.

    the problem comes, when you have another city, which also requires more than one vaccine, but,
    now, for that other case you have some employer who took yet just some part of the total of the required vaccines.

    See the table below -- which the output is not so ok.


    +----------+---------------------+--------------+
    | cityName | travelerFirstName | vaccineName |
    +----------+---------------------+--------------+
    | Miami | Paul | Yellow Fever |
    | Miami | Jack | Yellow Fever |
    | Miami | Bill | Yellow Fever |
    | Miami | Leonidas | Yellow Fever |
    | Miami | Mark | Influenza |
    | Miami | Jack | Influenza |
    | Miami | Leonidas | Influenza |
    | Miami | Mark | Hepatitis |
    | Miami | Jack | Hepatitis |
    | Miami | Leonidas | Hepatitis |
    | Miami | Joe | Hepatitis |
    +----------+---------------------+--------------+

    Suppose that in this example the city requires triple vaccine. For example, Paul, Bill, Joe, Mark, just took part of it, so
    they should not appear in the list for this city requiring the triple vaccine.

    On the other hand, Leonidas and Jack are ok, because they took the triple vaccine.

    So, any suggestion??

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by threaderslash View Post
    So, any suggestion??
    yes, but i already gave it to you --- GROUP BY with HAVING
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    Yep! As I told you... trying the suggestion you gave looks nice with --- GROUP BY with HAVING. But it gives that same error I told you in one of my previous posts, although the syntax looks ok. Just funny... Any hints on why?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    oh, sorry, my bad -- i can't believe i had the WHERE clause in the wrong place

    this should work...
    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
     WHERE city.cityName = 'Boston'
    GROUP
        BY traveler.travelerFirstName 
    HAVING COUNT(*) =
           ( SELECT COUNT(*)
               FROM city
             INNER
               JOIN requiredvaccine
                 ON requiredvaccine.cityID = city.cityID
              WHERE city.cityName = 'Boston' )
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb

    Awesome... Thanks man! You saved the day.

    I did some changes to properly couple with my data then it worked nicely!

    Here is the final mysql 5 tables relationship INNER JOIN running. So nice....

    Code:
    SELECT city.cityName,traveler.travelerFirstName from
    (city INNER JOIN requiredvaccine ON city.cityID = requiredvaccine.cityID)
    	    INNER JOIN vaccine ON requiredvaccine.vaccineID=vaccine.vaccineID
    	    INNER JOIN takenvaccine ON vaccine.vaccineID=takenvaccine.vaccineID
    	    INNER JOIN traveler ON takenvaccine.travelerID=traveler.travelerID
    WHERE city.cityName = 'Miami'
    GROUP
        BY traveler.travelerFirstName 
    HAVING COUNT(*) =
    (	SELECT COUNT(*) FROM
    	(city INNER JOIN requiredvaccine ON city.cityID = requiredvaccine.cityID)
    	    INNER JOIN vaccine ON requiredvaccine.vaccineID=vaccine.vaccineID
    	WHERE city.cityName = 'Miami'
    );
    ...the output:

    Code:
    +----------+---------------------+
    | cityName | travelerFirstName |
    +----------+---------------------+
    | Miami    | Jack                |
    | Miami    | Leonidas            |
    +----------+---------------------+
    Cheers!

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    can i ask why you put parentheses back around some of those joins after i took them away?

    also, why did you unnecessarily join to the vaccine table in both the subquery and the main query?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb

    About the parenthesis, they are not necessary indeed. You're right. Thanks!

    About the vaccine table.. if we code this way:

    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
     WHERE city.cityName = 'Miami'
    GROUP
        BY traveler.travelerFirstName 
    HAVING COUNT(*) =
           ( SELECT COUNT(*)
               FROM city
             INNER
               JOIN requiredvaccine
                 ON requiredvaccine.cityID = city.cityID
              WHERE city.cityName = 'Miami' );
    we get the following result from the example described yet:
    Code:
    Empty set (0.00 sec)
    which is not the expected proper output from the current given data.

    but, doing instead:

    Code:
    SELECT city.cityName,traveler.travelerFirstName from
    city INNER JOIN requiredvaccine ON city.cityID = requiredvaccine.cityID
    	    INNER JOIN vaccine ON requiredvaccine.vaccineID=vaccine.vaccineID
    	    INNER JOIN takenvaccine ON vaccine.vaccineID=takenvaccine.vaccineID
    	    INNER JOIN traveler ON takenvaccine.travelerID=traveler.travelerID
    WHERE city.cityName = 'Miami'
    GROUP
        BY traveler.travelerFirstName 
    HAVING COUNT(*) =
    (	SELECT COUNT(*) FROM
    	city INNER JOIN requiredvaccine ON city.cityID = requiredvaccine.cityID
    	    INNER JOIN vaccine ON requiredvaccine.vaccineID=vaccine.vaccineID
    	WHERE city.cityName = 'Miami'
    );
    it results in:
    Code:
    +----------+---------------------+
    | cityName | travelerFirstName |
    +----------+---------------------+
    | Miami    | Jack                |
    | Miami    | Leonidas            |
    +----------+---------------------+
    2 rows in set (0.00 sec)
    which is the right output from the current data.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    in that case there's probably something wrong with your data
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb

    apparently it is well organized.

    It does not repeat foreignKeys accross all the relationships. It has the minimum of tables links as necessary.

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

    Code:
    * 'requiredvaccine' has the following foreignKeys
    [city]1----*----1[vaccine]1
    
    and
    * 'takenvaccine' has the following foreignKeys
    [vaccine]1----*----1[traveler]
    It also has worked well for all other tests done so far.

  13. #13
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Double 'five INNER JOIN' nested subquery

    Hi Everybody,

    I am trying to do a [double 'five INNER JOIN' nested subquery]

    It consists to inform what are the travelers from a given company who attend ALL 'Vaccine' and 'security' requirements to go to a determined city.

    - given the following E-R/tables set
    // tables: [city]1----n[requiredvaccine]n----1[vaccine]1----n[takenvaccine]n----1[traveler]
    // tables: [city]1----n[requiredsecurity]n----1[security]1----n[takensecurity]n----1[traveler]

    Both nested queries have in common just the two tables - which are placed at the beginning and end of the relationships:[city]1----n[...]n----1[traveler]

    - given that:
    the first '5 INNER JOIN query' is working nicely - ( the same about the second '5 INNER JOIN' set; see http://www.sitepoint.com/forums/show...=1#post4350970 great r937 - thnx! )
    // we have 2 employees that attend full vaccine requirements

    The correct output:
    Code:
    +----------+---------------------+
    | cityName | travelerFirstName |
    +----------+---------------------+
    | Miami    | Jack                |
    | Miami    | Leonidas            |
    +----------+---------------------+
    - given that:

    // we have 4 employees that attend full security check requirements

    The correct output:
    Code:
    +----------+---------------------+
    | cityName | travelerFirstName |
    +----------+---------------------+
    | Miami    | Jack                |
    | Miami    | Joe                 |
    | Miami    | Mark                |
    | Miami    | Ralph               |
    +----------+---------------------+
    // then.. we have only 1 employee that attend full security and full vaccine check requirements
    Code:
    +----------+---------------------+
    | cityName | travelerFirstName |
    +----------+---------------------+
    | Miami    | Jack                |
    +----------+---------------------+
    So, for this output, the code option for double 'five INNER JOIN' nested subquery would look something similar to the following:

    // inform who attend ALL 'Vaccine' and ALL 'security' requirements to go to Miami.
    Code:
    SELECT city.cityName,traveler.travelerFirstName,traveler.travelerID from
    (city INNER JOIN requiredsecurity ON city.cityID = requiredsecurity.cityID)
    	    INNER JOIN security ON requiredsecurity.securityID=security.securityID
    	    INNER JOIN takensecurity ON security.securityID=takensecurity.securityID
    	    INNER JOIN traveler ON takensecurity.travelerID=traveler.travelerID
    WHERE city.cityName = 'Miami' && 
    		traveler.travelerID LIKE
    		(
    			SELECT traveler.travelerID from
    			(city INNER JOIN requiredvaccine ON city.cityID = requiredvaccine.cityID)
    	    			INNER JOIN vaccine ON requiredvaccine.vaccineID=vaccine.vaccineID
    	    			INNER JOIN takenvaccine ON vaccine.vaccineID=takenvaccine.vaccineID
    	    			INNER JOIN traveler ON takenvaccine.travelerID=traveler.travelerID
    			WHERE city.cityName = 'Miami'
    			GROUP
        			BY traveler.travelerFirstName 
    			HAVING COUNT(*) =
    			(	SELECT COUNT(*) FROM
    				(city INNER JOIN requiredvaccine ON city.cityID = requiredvaccine.cityID)
    	    			INNER JOIN vaccine ON requiredvaccine.vaccineID=vaccine.vaccineID
    				WHERE city.cityName = 'Miami'
    			)
    
    		)
    GROUP
        BY traveler.travelerID 
    HAVING COUNT(*) =
    (	SELECT COUNT(*) FROM
    	(city INNER JOIN requiredsecurity ON city.cityID = requiredsecurity.cityID)
    	    INNER JOIN security ON requiredsecurity.securityID=security.securityID
    	WHERE city.cityName = 'Miami'
    );
    However the MySQL complains - about the expression after the '&&':
    Code:
    ERROR 1242 (21000): Subquery returns more than 1 row
    that indeed has to return more than one result.

    My question is, there is some way out on MySQL to implement this functionality (which would look like a switch.. case), or the only way out is to keep the first and second main '5 INNER JOIN' queries separated, and use the help of some programming language (like C++/Java) to make this type of relationship/query connection?

    All comments are mostly welcome and highly appreciated.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i wasn't disputing the need for the vaccine table, nor was i raising a question about your table design

    all i'm suggesting is that in the query i gave you, it is not necessary to join to the vaccine table
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot man. As I said before, you saved the day! Cheers.


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
  •