CODEWITHSUNDEEP
sqlsqlite
user32882
user32882

Reputation: 5877

select from where field not equal to field in other table

I am running the following script in SQLite3:

drop table if exists B;
drop table if exists C;

create table B (a integer, b text, c text);

insert into B values (1, "1.1", "B");
insert into B values (1, "2.1", "B");
insert into B values (3, "3.1", "B");
insert into B values (4, "4.1", "B");
insert into B values (5, "5.1", "B");
insert into B values (6, "6.1", "B");
insert into B values (7, "7.1", "B");

create table C (a integer);

insert into C (a) values (1), (3), (5);

select * from B where B.a <> C.a;

This script throws an error when I run it:

Error: near line 30: no such column: C.a

The idea here is that I would like to make a selection from B where field a is not equal to 1, 3 or 5. Is it not possible to refer to columns from other tables within the same SQL statement? How to achieve this?

EXPECTED RESULT

a|b|c
4|"4.1"|"B"
6|"6.1"|"B"
7|"7.1"|"B"

Upvotes: 1

Views: 3897

Answers (2)

forpas
forpas

Reputation: 164099

This requirement can be expressed with NOT EXISTS like this:

select * from B 
where not exists (
  select 1 from C
  where C.a = B.a
);

For every row of B if B.a does not exist in C then NOT EXISTS is TRUE and that row is returned.

Or with a LEFT JOIN from which only the unmatched rows of B are returned:

select B.*
from B left join C
on C.a = B.a
where C.a is null;

See the demo.
Results:

| a   | b   | c   |
| --- | --- | --- |
| 4   | 4.1 | B   |
| 6   | 6.1 | B   |
| 7   | 7.1 | B   |

Upvotes: 2

tonypdmtr
tonypdmtr

Reputation: 3225

SELECT * FROM B WHERE a NOT IN (SELECT DISTINCT a FROM C)

This is standard SQL.

NOT IN excludes all values from the selected set. The set can be from another table, not necessarily a list of constant values.

DISTINCT is optional but it may produce faster execution in very large sets.

Upvotes: 1

Related Questions