mysql - SQL SELECT DISTINCT 子查询错误 : 1241

Question

我正在处理一个包含 SELECT DISTINCT 和子查询的长查询。它部分起作用，但放在一起时不起作用。我收到错误:1241 - 操作数应包含 1 列，我不明白为什么。任何帮助将不胜感激。

这是我的代码:

SELECT p.projId, pc.company, s.staffId, s.fName, s.lName
    FROM projects AS p
    INNER JOIN projCorp AS pc ON p.projId = pc.projId
    INNER JOIN projStaff AS ps ON p.projId = ps.projId
    INNER JOIN staff AS s ON ps.staffId = s.StaffId
    WHERE p.projId = '9' AND s.company = pc.company
UNION
    SELECT DISTINCT p.projId, pc.company, "NA", "NA", "NA"
    FROM projects AS p
    INNER JOIN projCorp AS pc ON p.projId = pc.projId
    INNER JOIN projStaff AS ps ON p.projId = ps.projId
    WHERE p.projId = '9' AND pc.company NOT IN (SELECT p.projId, pc.company, s.staffId, s.fName, s.lName
        FROM projects AS p
        INNER JOIN projCorp AS pc ON p.projId = pc.projId
        INNER JOIN projStaff AS ps ON p.projId = ps.projId
        INNER JOIN staff AS s ON ps.staffId = s.StaffId
        WHERE p.projId = '9' AND s.company = pc.company);

没有 NOT IN 子查询，我得到这个结果:

NOT IN 子查询的原因是去除没有关联职员的第三个重复条目。结果应该只有图片结果字段中的前两个条目。

Answer 1

截图:

... WHERE p.projId = '9' AND pc.company NOT IN (
        SELECT p.projId, pc.company, s.staffId, s.fName, s.lName
        FROM projects AS p ....
    )

您正在尝试选择何时 company not in 但在您的 not in 查询中指定了多个选定行。只需选择公司:

... WHERE p.projId = '9' AND pc.company NOT IN (
        SELECT pc.company
        FROM projects AS p ....
    )