在这个查询中使用左外连接，困惑，不确定我做了正确的方法

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 find out the last name, first name and ssn of all female managers who worked less than 5 projects and located in Detroit.

我想找出工作不到5个项目并位于底特律的所有女性经理的姓氏，名字和ssn。

I have this so far:

到目前为止我有这个：

select lname
from employee e, department d
where (e.ssn = d.mgrssn) 
and  ssn in  (
                       select  w.essn
                       from   works_on w, project p
                       left join w.pno = p.pnumber
                       and  p.plocation = 'Detroit'
                       group by w.essn
                       having count(*) <= 5
                     )

I'm not sure about my left join commands, did I do it right?

我不确定我的左连接命令，我做对了吗？

2 个解决方案

#1

Learn to use explicit JOIN syntax. Simple rule: Never use a comma in a FROM clause.

学习使用显式JOIN语法。简单规则：永远不要在FROM子句中使用逗号。

Your query would be properly written as:

您的查询将被正确编写为：

select lname
from employee e join
     department d
     on e.ssn = d.mgrssn and
where e.ssn in  (select  w.essn
                 from works_on w left join
                      project p
                      on w.pno = p.pnumber and  p.plocation = 'Detroit'
                 group by w.essn
                 having count(*) <= 5
                );

Note: this is a syntactic statement about your query, not about the logic and what it does.

注意：这是关于查询的语法语句，而不是逻辑及其作用。

#2

It's a little hard without seeing data, I 100% agree with not using commas, I also am not sure why you want to use a left join with this as you would only get the results that satisfied the conditions you laid out anyway. You have no need to preserve null values in this scenario as far as I can tell. You may need to clarify slightly more as to why you need a left join, I added them in this anyway just because.

没有看到数据有点困难，我100％同意不使用逗号，我也不确定你为什么要使用左连接，因为你只能得到满足你所设置的条件的结果。就我所知，您无需在此方案中保留空值。您可能需要稍微澄清一下为什么需要左连接，我在这里添加它们只是因为。

SELECT lname,
   fname,
   ssn
FROM Employee
WHERE SEX = 'F'
AND SSN IN( 
          SELECT mgrssn
          FROM department d
               LEFT JOIN works_on w ON d.mgrssn = w.essn
               LEFT JOIN project p ON p.pnumber = w.pno
          WHERE p.plocation = 'Detroit'
          GROUP BY w.essn
          HAVING COUNT(*) < 5 );

#1