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. 
MySQL five tables INNER JOIN
a little better at least.
... almost. 
Originally Posted by threaderslash
Bookmarks