Getting an error when using joins in query

Question

I have this query:

SELECT * FROM `employee_activities` a
LEFT JOIN `activity` b ON a.activity_code = b.code
LEFT JOIN `employees` c ON a.employee_code = c.code
WHERE b.type = "Design"
AND c.code NOT IN(
    SELECT * FROM `employee_activities` a
        LEFT JOIN `activity` b ON a.activity_code = b.code
        LEFT JOIN `employees` c ON a.employee_code = c.code
        WHERE b.type = "Testing"
)
GROUP BY c.code

I get this error:

#1241 - Operand should contain 1 column(s)

I'm tying to get all employees that have at least one activity of type "Design" and None activity of type "Testing".

I have a query that works but I would like it to work with joins.

This works:

SELECT c.name FROM `employee_activities` a, `activity` b, `employees` c
WHERE a.activity_code = b.code
AND a.employee_code = c.code
AND b.type = "Design"

AND c.code NOT IN(
    SELECT c.code FROM `employee_activities` a, `activity` b, `employees` c
        WHERE a.activity_code = b.code
        AND a.employee_code = c.code
        AND b.type = "Testing"
)
GROUP BY c.code

What did I do wrong on the sql with joins?

Answer 1

For the not in sub query - it should contain only one column - for example

SELECT * FROM `employee_activities` a
LEFT JOIN `activity` b ON a.activity_code = b.code
LEFT JOIN `employees` c ON a.employee_code = c.code
WHERE b.type = "Design"
AND c.code NOT IN(
    SELECT b.employee_code FROM `employee_activities` a
        LEFT JOIN `activity` b ON a.activity_code = b.code
        LEFT JOIN `employees` c ON a.employee_code = c.code
        WHERE b.type = "Testing"
)
GROUP BY c.code