I need to select information form a database based on criteria that crosses multiple tables

Question

I have a problem where I need to select the Project Number, Controlling Department Number, Department Manager's Lname, address, and birthdate for each project located in Stafford. I am having trouble getting the results I want.

I tried:

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

And Got:

+---------+------+---------+-------------------------+-----------+
| 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 |
+---------+------+---------+-------------------------+-----------+

But what I should have gotten is (or what I wanted):

+---------+------+---------+-------------------------+-----------+
| 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 |
+---------+------+---------+-------------------------+-----------+

Can anyone help me figure out what is wrong with my sql statement? sorry I wasn't able to figure out how to format this

Answer 1

Basically, you're missing out the join on DEPARTMENT and PROJECT.

I'd use explicit joins rather than the outdated where syntax:

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'

But with the old syntax:

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

Answer 2

Try something like this

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'