坚持写这个嵌套的子查询，混淆了加入表的一步

Employee(fname, minit, lname, SSN, bdate, address, sex, salary, superssn, dno); fname,minit,lname is the employee's name; bdate is birth date; superssn is supervisor's social security #; dno is dept #
Department(dname, DNUMBER, mgrssn, mgrstartdate); mgrssn is manager ssn
Dept_locations(DNUMBER, DLOCATION); dlocation is department location
Project(Pname, PNUMBER, plocation, dnum)
Works_on(ESSN, PNO, hours); ESSN is employee ssn, pno is project number
Dependent(ESSN, DEPENDENT_NAME, sex, bdate, relationship)

I want to list last and first name, SSN of all managers who is NOT working on project 7 and located in Detroit

我想列出所有未参与项目7并位于底特律的经理的姓氏和名字的SSN

This look kind right:

这看起来很对吧：

Select e.ssn, e.lname, e.fname
from employee e, department d
where (d.mgrssn = e.ssn)

where e.ssn NOT in (
        select w.essn
        from works_on w
        where w.pno = '07'
        and p.plocation = 'Detroit'
)

I think I need to put a pno = works_on joint statement before pno = 07, but someones told me I don't need it. So i'm really confused now.

我想我需要在pno = 07之前放一个pno = works_on联合声明，但有人告诉我我不需要它。所以我现在真的很困惑。

Also do I need to include where (d.mgrssn = e.ssn) in a bracket or not?

我是否还需要在括号中包含（d.mgrssn = e.ssn）？

1 个解决方案

#1

Here is the query in long form with joins:

以下是带有连接的长形式查询：

select e.ssn, e.lname, e.fname
from employee e
  join works_on wo on wo.ESSN = e.ssn
  join project PO on po.pnumber = wo.pno
  join dept_location dl on dl.dnumber = po.dnum
where dl.dlocation != 'Detroit'
  or po.pnumber != 7

Here is the same query with a not in, in case you want to avoid a large number of joins:

如果你想避免大量的连接，这里是一个不带in的查询：

select e.ssn, e.lname, e.fname
from employee e
where e.ssn not in
(select wo.essn from works_on wo
  join dept_locations dl on dnumber = wo.pno
  where dl.dlocation = 'Detroit'
    or po.pnumber = 7)

Hope that helps.

希望有所帮助。

#1