Query

Hi, I have the following tables-
How do I answer this query?

EMPLOYEE
ssn ename dnumber

DEPARTMENT
dnumber dname

PROJECT
pnumber dnumber

WORKS_ON
ssn pnumber hours

For each employee, find the department he works in and the total hours he spent on projects not controlled by the department he is working on.
Ie, he works on some extra projects not controlled by his department.

Thanks.This should get you going

SELECT ed.ename, ed.dname, sum(pw.hours)
FROM
(SELECT e.ssn, e.ename, d.dname, e.dnumber
FROM employee e, department d
WHERE e.dnumber = d.dnumber) ed,
(SELECT ssn, dnumber, hours
FROM works_on w, project p
WHERE w.pnumber = p.pnumber) pw
WHERE ed.ssn = pw.ssn
AND ed.dnumber != pw.dnumber
GROUP BY ed.ename, ed.dname
;|||Hi,

SELECT e.ename
, e.dname
, sum(p.hours)
FROM
employee e
, department d
, project p
, works_on w
WHERE
e.dnumber = d.dnumber
and e.ssn = w.ssn
and w.pnumber = p.pnumber
GROUP BY e.ename
;|||Originally posted by gannet
This should get you going

SELECT ed.ename, ed.dname, sum(pw.hours)
FROM
(SELECT e.ssn, e.ename, d.dname, e.dnumber
FROM employee e, department d
WHERE e.dnumber = d.dnumber) ed,
(SELECT ssn, dnumber, hours
FROM works_on w, project p
WHERE w.pnumber = p.pnumber) pw
WHERE ed.ssn = pw.ssn
AND ed.dnumber != pw.dnumber
GROUP BY ed.ename, ed.dname
;


Thanks,
That was a great solution!!
:-)