MySQL five tables INNER JOIN

[threaderslash]

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!!!

[r937]

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

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';

[threaderslash]

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?..

[threaderslash]

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? :find: … 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??

[r937]

@threaderslash

So, any suggestion??

yes, but i already gave it to you — GROUP BY with HAVING

[threaderslash]

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?

[r937]

oh, sorry, my bad – i can’t believe i had the WHERE clause in the wrong place

this should work…

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
 [COLOR="Red"]WHERE city.cityName = 'Boston'[/COLOR]
GROUP
    BY traveler.travelerFirstName
HAVING COUNT(*) =
       ( SELECT COUNT(*)
           FROM city
         INNER
           JOIN requiredvaccine
             ON requiredvaccine.cityID = city.cityID
          WHERE city.cityName = 'Boston' )

[threaderslash]

Awesome… Thanks man! :award::award::award::award::award: 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…:D:D:D

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:

+----------+---------------------+
| cityName | travelerFirstName |
+----------+---------------------+
| Miami    | Jack                |
| Miami    | Leonidas            |
+----------+---------------------+

Cheers! :D:D:D:D:D

[r937]

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?

[threaderslash]

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

About the vaccine table… if we code this way:

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:

Empty set (0.00 sec)

which is not the expected proper output from the current given data.

but, doing instead:

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:

+----------+---------------------+
| cityName | travelerFirstName |
+----------+---------------------+
| Miami    | Jack                |
| Miami    | Leonidas            |
+----------+---------------------+
2 rows in set (0.00 sec)

which is the right output from the current data.

[r937]

in that case there’s probably something wrong with your data

[threaderslash]

apparently it is well organized. :juggle:

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]

* '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.

[threaderslash]

Hi Everybody,

I am trying to do a [double ‘five INNER JOIN’ nested subquery] :magic:

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. :juggle:

• 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/showthread.php?p=4350970&posted=1#post4350970 great r937 - thnx! :award: :good: )
  // we have 2 employees that attend full vaccine requirements

The correct output:

+----------+---------------------+
| cityName | travelerFirstName |
+----------+---------------------+
| Miami    | Jack                |
| Miami    | Leonidas            |
+----------+---------------------+

• given that:

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

The correct output:

+----------+---------------------+
| 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

+----------+---------------------+
| 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.

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 ‘&&’:

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.

[r937]

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

[threaderslash]

Thanks a lot man. As I said before, you saved the day! Cheers.