有关联接的SQL语句

问题:对于所有与上级居住在不同城市的工作人员,将其工作人员的姓名列出为“ staff_member”,将主管的姓名列出为“ supervisor”。

关系:

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.

到目前为止,我得到的是:

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)

我做错了什么?

Error:Your query has syntax errors.
Description:java.sql.SQLException: ORA-00920: invalid relational operator
a546335402 回答:有关联接的SQL语句

您需要一个self join。 我正在考虑supervisor表中的staff列指向snum表中supervisor的{​​{1}}。

staff

干杯!

,

尝试一下:

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 
)
本文链接:https://www.f2er.com/3159777.html

大家都在问