List the the name of employee that worked on more than one projects
oracle11g, sql
Solution
your group by was returning one row per employee per project, by definition this could never have more than 1 row
below sql should work
WARNING: i am not sure how this will affect performance of server use at your own risk
following will return 1 row per employee per workon but be limited to employees with more than 1 workon record (so if employee has 5 workon records, you will get 5 rows with same e.name and then 5 different w.pid values)
select e.name, w.pid
from employee e, workon w
where e.empid = w.empid
and e.empid in (
select w.empid
from workon w -- there was a typo here originally
group by 1
having count (*) > 1
)
order by e.name, w.pid
Problem
There are two tables that would be relevant in this. Workon and Employee. The contents of these two tables are EMPID NAME SALARY DID (department ID) for employee and PID EMPID HOURS from work on. The SQL I have written is ``` select e.name, w.pid from employee e, workon w where e.empid = w.empid group by e.name, w.pid, w.empid having count (e.name) > 1 order by w.pid ``` I have been trying to figure out why this code will not give me employees that work on more than one project. Please help me figure out what I am doing wrong.