SQLite Forum

How to select entries from second table that either do not have an entry or do not have a specific entry?
Login

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 [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 [link] [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!