subquery returning multiple rows..how to deal with it?

mysql, subquery

Solution

It would be much clearer to write this query using `JOIN`:

select distinct r.requestID 
from 
    request r
    join department d
        on d.userId = r.userID
        and desig = 'E'
    join department d2
        on d2.dept = d.dept
        and d2.desig = 'FM'
        and d2.userId = 'it18'

Alternately, You could simply replace the `=` with `IN`:

SELECT requestID
FROM request
WHERE userId IN (
    SELECT userID
    FROM department
    WHERE desig = 'E'
    AND dept IN (
        SELECT dept
        FROM department
        WHERE userId = it18
        AND desig = 'FM'
      )
);

They should return identical results, but try both to see if there's any difference in performance.

Problem

Original source