CODEWITHSUNDEEP
mysqlsqldatabase
Sidney Heier
Sidney Heier

Reputation: 97

How to get rid of Error Code 1242

I have the lines of code in My SQL Workbench, but I am receiving the error: Error Code: 1242. Subquery returns more than 1 row.

 SELECT Fname, Lname, Dnum, Pno 
 FROM EMPLOYEE e, PROJECT p, WORKS_ON w 
 WHERE p.Dnum = (SELECT Dno FROM EMPLOYEE WHERE e.Ssn = w.Essn AND w.Pno = p.Pnumber)

I am trying to display a table of employees that are working on a project that is not sponsored by their department.

Upvotes: 1

Views: 1415

Answers (2)

Singh Kailash
Singh Kailash

Reputation: 674

Optimized query:

SELECT Fname, Lname, Dnum, Pno 
FROM EMPLOYEE e
INNER JOIN  WORKS_ON  w ON e.Ssn = w.Essn
INNER JOIN  PROJECT  p  ON  w.Pno = p.Pnumber AND p.Dnum = e.Dno

Upvotes: 0

denny
denny

Reputation: 2254

try this 

SELECT Fname, Lname, Dnum, Pno 
FROM EMPLOYEE e, PROJECT p, WORKS_ON w 
WHERE p.Dnum in (SELECT Dno FROM EMPLOYEE WHERE e.Ssn = w.Essn AND w.Pno = 
p.Pnumber);

use in clause instad =

Upvotes: 1

Related Questions