左外加入sql查询

The database:

EMPLOYEE (fname, minit, lname, ssn, birthdate, address, sex, salary, superssn, dno)     KEY: ssn
DEPARTMENT (dname, dnumber, mgrssn, mgrstartdate)               KEY: dnumber.
PROJECT  (pname, pnumber, plocation, dnum)                          KEY: pnumber.
WORKS_ON (essn, pno, hours)                                         KEY: (essn, pno)
DEPENDENT  (essn, dependent-name, sex, bdate, relationship)             KEY: (essn, dependent-name)

I want to use left outer join and group by to...
Find the last name and SSN of those managers who work on 3 or more projects and who are not located in Cleveland.

我想使用左外连接和分组来...找到那些在3个或更多项目上工作但不在克利夫兰的经理的姓氏和SSN。

Here is what I have so far:

这是我到目前为止:

select Lname
  from Employee e outer join Department d
where (e.ssn = d.mgrssn) 
   and  ssn NOT in (
                      select  w.essn
                       from   works_on w outer join Project p
                      where w.pno = p.pnumber
                          and  p.plocation = 'Cleveland'
                      group by w.essn
                      having count(*) >= 3
                     )

Did I do it right using left outer join and group by? Should I divide this code into two parts, like loops?

我是否正确使用左外连接和分组?我应该将此代码分为两部分,如循环吗?

1 个解决方案

#1

Select JOIN find all project for the employee

选择JOIN查找员工的所有项目

First HAVING tell you this user doesnt have project in 'Cleveland'

首先告诉你这个用户在“克利夫兰”没有项目

Second HAVING tell you this user has 3 project or more

第二个HAVING告诉你这个用户有3个项目或更多

SELECT e.Lname, e.ssn
FROM Employee e   
JOIN works_on w
  ON e.ssn = w.essn
JOIN Project p
  ON w.pno = p.pnumber
GROUP BY e.ssn
HAVING 
     SUM(CASE WHEN p.plocation = 'Cleveland' THEN 1 ELSE 0 END) = 0
AND  COUNT(*) >= 3

#1