Sql query with left join confusion

Question

While I was taking a technical exam for the position of database developer I faced a question :

Display all the details for species that are not covered by any specialty of employees working for zoos in England.

Tables:

Zoo: ZooName,City,Country,City
Animal: AnimalId,Species,ZooName,Gender,Dob
Employee:EmployeeId,FirstName,LastName,Gender
EmployeeContract: EmployeeId,ZooName,Salary,StartDate,EndDate
Species: Species,Status,TotalAnimals
Specialty:EmployeeId,Species

I didn't understood the part of the question which says "that are not covered by any specialty of employees". I believe there has to be left join between Species and Specialty table because we will find the species for which there is no employees against that species and basically that will be our species for which there is no employee but I am not sure whether I am thinking in a right direction or not.

I am finding hard to figure out the logic for this. Can anybody please explain me the logic for this, please

Please don't ask us to write your code. Show what parts or relevant queries you can do. What are the employees? What are the specialties? Employees & their specialties? What are the species of the zoo? What are the animals with their species? Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] — philipxy, Mar 12 '20 at 06:37
[Re relational querying.](https://stackoverflow.com/a/55671890/3404097) — philipxy, Mar 12 '20 at 21:12

score 1 · Answer 1 · answered Mar 12 '20 at 06:44

First let's take each table field which are used in another table:

table Zoo ZooName is used in Animal table.
table Animal Species is used in Species and Specialty table.
table Employee EmployeeId is used in EmployeeContract and Specialty table.

The question is to display the details for species that are not covered by any specialty of employees working for zoos in England.

I believe what they're asking was species data which are registered in Specialty table where the employee who handles the animal registered not in England Zoo.

You could do something like:

Select s.Species, s.Status, s.TotalAnimals -- using Species table as select since the question ask for all the details for species
From Species s -- get species data
Left Join Specialty sp ON s.Species = sp.Species -- get specialty data
Left Join Animal a ON s.Species = a.Species -- get animal data
Left Join Zoo z on a.ZooName = z.ZooName -- get zoo data
Where Zoo.Country <> 'England' -- not in England Zoo

Sql query with left join confusion

1 Answers1