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]

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]

Either a join with suitable criteria, or [the compound operator](https://sqlite.org/syntax/compound-operator.html), "EXCEPT", will do that trick.

(3) By Larry Brasfield (LarryBrasfield) on 2020-11-04 18:31:37 in reply to 2.1

For example:<code>
 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');
</code>

(4) By Keith Medcalf (kmedcalf) on 2020-11-04 18:46:37 in reply to 3 [link]

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]

Thank you guys VERY MUCH! The except did the job!