List faculty members who have smaller or equal salary than their supervisor by using SELF(RECURSIVE) JOIN

Question

Below is the table Faculty

This is the query question from my assignment which is shown below:

List faculty members who have a smaller or equal salary than their supervisor. List the Social Security number, last name and salary of the faculty and supervisor. (Hint: use a recursive join)

This is how I approached which is shown below:

SELECT Faculty.FacSSN, Faculty.FacLastName, Faculty.FacSalary, Faculty.FacSupervisor, S.FacLastName, S.FacSalary
FROM Faculty, Faculty S
WHERE NOT Faculty.FacSSN = Faculty.FacSupervisor
AND Faculty.FacSalary <= S.FacSalary
AND NOT Faculty.FacLastName = S.FacLastName
AND Faculty.FacSupervisor IS NOT NULL;

This is the following result I'm getting which is shown below

I'm getting the result of Supervisor's salary greater than or equal to Faculty member's salary. But the problem is I'm also getting result of Faculty member's salary (who are not supervisors) being greater than or equal to other Faculty member's salary.

How do I solve this problem ?

Can someone correct my SQL code ?

Answer 1

Access does not support recursive joins, you will have to switch the DBMS for that. Also see Is it possible to create a recursive query in Access?

That said, this problem doesn't seem to need recursive joins, because only the direct supervisor is needed, not the whole hierarchy to the top.

Looking at your query I'm not sure what you were going for using WHERE NOT Faculty.FacSSN = Faculty.FacSupervisor (There is no one being their own supervisor), but if you change that to WHERE S.FacSSN = Faculty.FacSupervisor it should work.

A cleaned up version would look like this:

SELECT F.FacSSN, F.FacLastName, F.FacSalary, F.FacSupervisor, S.FacLastName, S.FacSalary
FROM Faculty AS F
INNER JOIN Faculty AS S ON S.FacSSN = F.FacSupervisor
WHERE F.FacSalary <= S.FacSalary