CODEWITHSUNDEEP
mysqlsql
Maarduk
Maarduk

Reputation: 256

SQL query, NOT IN returns an error

Hello guys i got a little problem. So I got 2 tables robot_calls and invoices. I'm trying to get ID of an invoice which is not in ID of robot_calls.invoice_id. I get an error 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)";

I'm not an sql expert, can someone enlight me please?

Cheers.

Upvotes: 2

Views: 162

Answers (5)

isaace
isaace

Reputation: 3429

A left join should work for this:

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

Upvotes: 1

Rakesh Soni
Rakesh Soni

Reputation: 10877

Try this

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

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

You need to select a single id column from your robot_calls table to do this query: WHERE invoice_id NOT IN (SELECT invoice_id FROM robot_calls)

Another alternative to this query is to use existential quantifier:

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'

Upvotes: 2

Emre Kabaoglu
Emre Kabaoglu

Reputation: 13146

Do you want something like that;

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

Upvotes: 1

Mittal Patel
Mittal Patel

Reputation: 2762

You can achieve by below:

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

Upvotes: 2

Related Questions