SQL 查询，NOT IN 返回错误

Question

大家好，我遇到了一个小问题。所以我得到了 2 张桌子robot_calls和发票。我正在尝试获取不在 robot_calls.invoice_id ID 中的发票 ID。我收到一个错误Unknown column in 'where clause.

SELECT * FROM invoices where `desc`='Invoice from mr. Robot' NOT IN
(SELECT * FROM invoices where robot_calls.invoice_id=invoices.id)";

我

不是SQL专家，有人可以启发我吗？

干杯。

Answer 1

您需要

从robot_calls表中选择一个id列来执行此查询： WHERE invoice_id NOT IN (SELECT invoice_id FROM robot_calls)

此查询的另一种替代方法是使用存在量词：

SELECT *
FROM invoices iv
WHERE NOT EXISTS (SELECT * FROM robot_calls rc WHERE rc.invoice_id=iv.invoice_id)
  AND iv.desc='Invoice from mr. Robot'

Answer 2

您可以通过以下方式实现：

SELECT *
FROM invoices
WHERE ID NOT IN (
        SELECT invoice_id
        FROM robot_calls
        )

Answer 3

你想要这样的东西吗？

SELECT * FROM invoices where `desc`='Invoice from mr. Robot' and invoice_id NOT IN
(SELECT invoice_id FROM robot_calls)

Answer 4

试试这个

SELECT * FROM invoices 
WHERE`desc`='Invoice from mr. Robot' 
AND ID NOT IN
(SELECT invoice_id FROM robot_calls)

Answer 5

左连接应该适用于此：

SELECT * FROM invoices i
LEFT JOIN robot_calls r ON r.invoice_id=r.id
WHERE i.desc = 'Invoice from mr. Robot'
AND r.invoice_id IS NULL