SQL statement about join

Question

Question: List the name of the staff member as 'staff_member' and the name of the supervisor as 'supervisor' for all staff members who do not live in the same city as their supervisor.

Relations:

Staff (Snum, Name, DOB, Address, City, Gender, Salary, Supervisor,Dnum) 
Dept ( Dnum, Dname, Manager, Mgrstartdate ) 
Deptlocation ( Dnum, Dcity ) 
Project ( Pnum, Pname, Pcity, Dnum ) 
Workson ( Snum, Pnum, Hours )

Column Supervisor of table Staff is a foreign key which references column Snum of table Staff.
Column Dnum of table Staff is a foreign key which references column Dnum of table Dept.
Column Manager of table Dept is a foreign key which references column Snum of table Staff.
Column Dnum of table Deptlocation is a foreign key which references column Dnum of table Dept.
Column Dnum of table Project is a foreign key which references column Dnum of table Dept.
Column Snum of table Workson is a foreign key which references column Snum of table Staff.
Column Pnum of table Workson is a foreign key which references column Pnum of table Project.

What I got so far:

SELECT name AS staff_member,  supervisor
from staff s
INNER JOIN deptlocation d ON s.dnum = d.dnum
WHERE s.city NOT EXISTS (d.city)

What am I doing wrong ?

Error:Your query has syntax errors.
Description:java.sql.SQLException: ORA-00920: invalid relational operator

Answer 1

You need a self join. I am considering that supervisor column in staff table points to snum of supervisor from staff table.

Select st.name as staff_member, 
       Sp.name as supervisor
  From staff st 
       Inner join staff sp 
       On (st.supervisor = sp.snum)
 Where st.city <> sp.city;

Cheers!!

Answer 2

try this:

SELECT name AS staff_member,  supervisor
from staff s
INNER JOIN deptlocation d ON s.dnum = d.dnum
WHERE NOT EXISTS (
select 1 from deptlocation aa
aa.city=s.city 
)