List the Employee details for any Employee that has the highest salary for their species expertise

The question is:

List the Employee details for any Employee that has the highest salary for their species expertise

It is to be solved using Employee and V1 table (v1 is a view created using employee table)

https://imgur.com/a/chIFHmT : first table is employee and second table is V1

Using the query below gives me all the right output beside it matches Tiger salary of 75000 while it should ONLY be showing Tiger Salary of 90000. (its showing both)

Expected Result:

 EmpNo  EmpName     Species     ZooName          Salary      Gender
 3008   Jill        Alligator   Whipsnade Park   40000.00      F
 3020   Red         Bear        Red Star         120000.00     M
 3004   Mary        Lion        Riverdale        110000.00     M
 3019   Alice       Panda       Pandaville       67000.00      F
 3013   May         Rhino       Boston City Zo   75000.00      F
 3006   Henrietta   Tiger       Whipsnade Park   90000.00      F

Current query:

select e.* 
from employee e 
join v1 on e.Species = v1.Species 
where e.Salary in (select salarymax from v1)

Actual Result I get When I run Query above:

EmpNo   EmpName     Species     ZooName          Salary      Gender
3008    Jill        Alligator   Whipsnade Park   40000.00     F
3020    Red         Bear        Red Star         120000.00    M
3004    Mary        Lion        Riverdale        110000.00    M
3019    Alice       Panda       Pandaville       67000.00     F
3013    May         Rhino       Boston City Zoo  75000.00     F
3003    John        Tiger       Riverdale        75000.00     M   <-- Issue
3006    Henrietta   Tiger       Whipsnade Park   90000.00     F

Answer

You are looking to pull out the employee that has the highest salary for each species. You could achieve this with just a NOT EXISTS condition and a correlated subquery:

SELECT e.*
FROM employee e
WHERE NOT EXISTS (
    SELECT 1
    FROM employee e1
    WHERE e1.species = e.species AND e1.salary > e.salary
)

If there are top ties (ie if more than one employee have the same, maximum salary for a given species), they will all be displayed.

NB: I don’t really see the point using the v1 view, so I did not.

Starting MySQL 8.0, window functions come into play, and this can be solved in a more efficient manner:

SELECT *
FROM (
    SELECT e.*, RANK() OVER(PARTITION BY species ORDER BY salary DESC) rn
    FROM employee
) x WHERE rn = 1

---

If you really want to use v1, then you would need to change you JOIN conditions. Consider:

SELECT e.* 
FROM employee e 
INNER JOIN join v1 on e.Species = v1.Species AND e.Salary = v1.salarymax