我有这个问题：
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
我收到此错误：
#1241 - Operand should contain 1 column(s)
我想要让所有至少有一个类型为“设计”活动且没有活动类型为“测试”的员工.
我有一个有效的查询但我希望它可以使用连接.
这有效：
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,`employees` c
WHERE a.activity_code = b.code
AND a.employee_code = c.code
AND b.type = "Testing"
)
GROUP BY c.code
我在连接的sql上做错了什么？
最佳答案
对于不在子查询中 – 它应该只包含一列 – 例如
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