基于跨越多个表的条件，我需要从数据库中选择信息。

Question

我有一个问题，需要选择每个位于斯塔福德的项目的项目编号、控制部门编号、部门经理的姓氏、地址和出生日期。我无法得到我想要的结果。

我尝试了：

SELECT PROJECT.PNUMBER, PROJECT.DNUM, EMPLOYEE.LNAME, EMPLOYEE.ADDRESS, EMPLOYEE.BDATE
FROM PROJECT, EMPLOYEE, DEPARTMENT
WHERE PLOCATION = 'STAFFORD' AND DEPARTMENT.MGRSSN = EMPLOYEE.SSN;

得到的查询结果如下：

+---------+------+---------+-------------------------+-----------+
| PNUMBER | DNUM | LNAME   | ADDRESS                 | BDATE     |
| 30      | 4    | WONG    | 683 VOSS, HOUSTON, TX   | 08-DEC-55 |
| 10      | 4    | WONG    | 683 VOSS, HOUSTON, TX   | 08-DEC-55 |
| 30      | 4    | WALLACE | 291 BERRY, BELLAIRE, TX | 20-JUN-41 |
+---------+------+---------+-------------------------+-----------+

但是我想要的结果应该是：

+---------+------+---------+-------------------------+-----------+
| PNUMBER | DNUM | LNAME   | ADDRESS                 | BDATE     |
| 10      | 4    | WALLACE | 391 BERRY, BELLAIRE, TX | 20-JUN-41 |
| 30      | 4    | WALLACE | 291 BERRY, BELLAIRE, TX | 20-JUN-41 |
+---------+------+---------+-------------------------+-----------+

有没有人能帮我找出我的SQL语句有什么问题呢？

- user2433662

---

已经有一段时间没有看到一个写得好的问题了。+1 - Kermit

因为这是一道作业题，原帖者贴了问题中的实体关系图。 - Daniel Gimenez

这是一道“作业”问题？！天哪，大学还在教授这种已经过时20年的语法？如果我是雇主，我不会给你机会。不巧的是，您面临的问题是您没有在FROM子句中使用现代语法来正确设置联接条件而已。 - RBarryYoung

一旦您复制粘贴了以下答案之一，请不要忘记在您的代码中放置最后一行，它大致如下 -- Powered By Stack Overflow。不要忘记放两个减号，否则代码会崩溃 :) - M.Ali

我只是在说他最好给我的答案点个赞，否则我就要告诉他的老师了。 - Daniel Gimenez

Answer 1

基本上，您遗漏了对DEPARTMENT和PROJECT的连接。

我建议使用显式连接而不是过时的where语法：

select
  PROJECT.PNUMBER,
  PROJECT.DNUM, 
  EMPLOYEE.LNAME,
  EMPLOYEE.ADDRESS
  -- and so on with the EMPLOYEE fields
from
  PROJECT
inner join
  DEPARTMENT
  on DEPARTMENT.DNUMBER = PROJECT.DNUM
inner join
  EMPLOYEE
  on EMPLOYEE.SSN = DEPARTMENT.MGRSSN
where
  PROJECT.PLOCATION = 'Stafford'

但是使用旧的语法：

select
  PROJECT.PNUMBER,
  PROJECT.DNUM, 
  EMPLOYEE.LNAME,
  EMPLOYEE.ADDRESS
  -- and so on with the EMPLOYEE fields
from
  PROJECT, DEPARTMENT, EMPLOYEE
where
  PROJECT.PLOCATION = 'Stafford'
  and DEPARTMENT.DNUMBER = PROJECT.DNUM -- This was the missing bit
  and EMPLOYEE.SSN = DEPARTMENT.MGRSSN

- T.J. Crowder

Answer 2

尝试使用类似以下的代码：

试试这样的代码

select p.pnumber, d.dnumber,e.lname, e.[address], e.bdate
from department D 
inner join project P on D.dnumber = P.dnum
inner join employee E on e.ssn = D.mgrssn
where p.location = 'STAFFORD'

- Milica Medic Kiralj

Answer 3

您的表连接不正确。您需要将部门与项目连接起来。

SELECT p.PNUMBER, p.DNUM, e.LNAME, e.ADDRESS, e.BDATE
FROM Project p
INNER JOIN Department d ON d.DNUMBER = p.DNUM
INNER JOIN Employee e ON e.ssn = d.MGRSSN
WHERE p.PLOCATION = 'STAFFORD';

也许你还没有学习其他的连接类型。基于你已经掌握的知识，这也应该可以工作：

SELECT PROJECT.PNUMBER, PROJECT.DNUM, EMPLOYEE.LNAME, EMPLOYEE.ADDRESS, EMPLOYEE.BDATE
FROM PROJECT, EMPLOYEE, DEPARTMENT
WHERE PLOCATION = 'STAFFORD' 
  AND DEPARMENT.DNUMBER = PROJECT.DNUM
  AND DEPARTMENT.MGRSSN = EMPLOYEE.SSN;

- Daniel Gimenez