How to select entries from second table that either do not have an entry or do not have a specific entry?
(1) By anonymous on 2020-11-04 17:01:40 [link] [source]
I'm trying to find entries in that either - do not have an entry at all - or do not have a specific entry Example: TABLE 1 Field A 100 101 102 TABLE 2 Field A. Field B 100 X 100. Y 100. Z 101. Y 101 Z The result should be: 101 102 Thanks a lot already!!!
(2.1) By Larry Brasfield (LarryBrasfield) on 2020-11-04 17:31:10 edited from 2.0 in reply to 1 [link] [source]
Either a join with suitable criteria, or the compound operator, "EXCEPT", will do that trick.
(3) By Larry Brasfield (LarryBrasfield) on 2020-11-04 18:31:37 in reply to 2.1 [source]
For example:
create table One (A integer);
create table Two (A integer, B text);
insert into One values (100);
insert into One values (101);
insert into One values (102);
insert into Two values (100, 'X');
insert into Two values (100, 'Y');
insert into Two values (100, 'Z');
insert into Two values (101, 'Y');
insert into Two values (101, 'Z');
select A from One except select A from Two where Two.B in ('X');
(4) By Keith Medcalf (kmedcalf) on 2020-11-04 18:46:37 in reply to 3 [link] [source]
Translated from the English description of how to solve the problem into SQL:
with cross
as (
select A, B
from (
select distinct A
from one
),
(
select distinct b
from two
)
)
select distinct a
from (
select *
from cross
except
select *
from two
)
;
Of course, "coding" the SQL is quite simple and proletarian once one has defined the problem solution (what programmers do).
(5) By anonymous on 2020-11-05 13:25:12 in reply to 3 [link] [source]
Thank you guys VERY MUCH! The except did the job!