AL-zami AL-zami - 6 months ago 9
SQL Question

find the male employees who works on more than one projects

I have the following schema:


employee(fname,lname,e_no,age,address,sex,slary,dept_number)

dpartment(dept_name,dept_number,dept_location)

project(pname,pnumber,plocation,dept_number)

works_on(e_no,pnumber,hours)


need to find the name of the male employees who works on more than one project.I created the following sql statement .But it is not working .What is my error and how i can fix it ?

SELECT `fname`,`lname`
FROM `employee`,`works_on`
ON`employee.`e_no`=`works_on`.`e_no`
WHERE `employee`.`sex`='male'
GROUP BY `employee`.`e_no`
HAVING COUNT(works_on.e_no)>1;

Answer

You should test by pnumber not by e_no in the HAVING clause:

SELECT fname, lname
FROM employee
JOIN works_on
ON employee.e_no = works_on.e_no
WHERE employee.sex = 'male' 
GROUP BY fname, lname
HAVING COUNT(DISTINCT works_on.pnumber) > 1;

Note: You should use explicit join syntax instead of old-style implicit syntax.

Comments