sql查询，not in返回错误

2skhul33 于 2021-06-21 发布在 Mysql

关注(0)|答案(5)|浏览(490)

大家好，我有点小问题。所以我有两张table，电话和发票。我正在尝试获取不在robot\u calls.invoice\u 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)";

我不是sqlMaven，有人能启发我吗？
干杯。

5条答案

你想要那样的东西吗；

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

您需要选择一个 id 来自您的 robot_calls 要执行此查询的表： 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'

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

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

左连接应适用于此：

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

试试这个

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