Why is this not working?

1

$sql="SELECT employee as SME, department, employee_title as title, SUM(w2_wages) as wages FROM employees e WHERE e.SME=1 AND e.department= (SELECT department FROM employees WHERE e.campaign='$campaign' AND e.email='$email' AND SME=1)";

I have a table called employees. Some of the employees are SME's. For each SME I'd like to total the w2_wages for everyone who in their same department. The error I am getting Message: Query Error >> [Subquery returns more than 1 row] . Any idea how to fix this?

2

run this with the proper values for the parameters $campaign and $email. What do you get?

SELECT department 
 FROM employees 
WHERE e.campaign='$campaign' 
AND e.email='$email' 
AND SME=1
3

Need to alias employees as e

1 Like
4

true. but i don't think that is the issue.

5

Actually what @mike01 mentioned could cause an issue. because e alias is from the previous employee query. irregardless. post back the question I asked you

6

Here is the actual error: Type: Exception

Message: Query Error >> [Subquery returns more than 1 row] >> Type: Exception

Message: Query Error >> [Subquery returns more than 1 row] >> SELECT employee as SME, department, employee_title as title, SUM(w2_wages) as wages FROM employees e WHERE e.SME=1 AND e.department= (SELECT department FROM employees e WHERE e.campaign='Apple' AND e.email='richb201@gmail.com' AND SME=1)

7

we already know that. the question was what is the result of the following if you ran it in some querying tool
I am sure you will get more than one department.

SELECT department 
   FROM employees e 
WHERE e.campaign='Apple' 
AND e.email='richb201@gmail.com' 
AND SME=1
8

I use mySQL but don't know how to set those two parameters. I have this line in my code and that is how I am generating the error. What I am trying to do is to list each SME and then total all the W2_wages from all of the employees who are in their department. Both the listing fo the SME's and all the other employees are in the same table, employees.

There are surely 4 or 5 employees that belong to the same department.

9

you cannot do this

AND e.department=

when the sub select after the = brings back more than 1 row.