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

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