Delete with join to multiple tables

Question

Code:

create table coltype (coltype varchar(5));

insert into coltype values ('typ1');

create table colsubtype (coltype varchar(5), colsubtype varchar(5));

insert into colsubtype values ('typ2', 'st1');
insert into colsubtype values ('typ2', 'st2');

create table table1 (col1 varchar(5), coltype varchar(5), colsubtype varchar(5));

insert into table1 values ('val1','typ1', 'st1');
insert into table1 values ('val2','typ1', 'st2');
insert into table1 values ('val3','typ1', 'st3');
insert into table1 values ('val4','typ2', 'st1');
insert into table1 values ('val5','typ2', 'st2');
insert into table1 values ('val6','typ2', 'st3');
insert into table1 values ('val7','typ3', 'st1');
insert into table1 values ('val8','typ3', 'st2');
insert into table1 values ('val9','typ3', 'st3');

commit;

Basically, I want to delete all records where the coltype and colsubtype is not mentioned in the coltype and colsubtype tables.

How do I do that. The below is path I was thinking of taking but it does not work - and - it does not seem like a good design.

delete from table1 
where coltype != (select coltype from coltype) 
    OR not (coltype = cst.coltype and colsubtype = cst.colsubtype 
from (select coltype,  colsubtype from colsubtype) cst)

Solution 1

Give this a try

delete from table1
where not exists
        (
        select *
        from coltype
        where table1.coltype = coltype.coltype
        )
    and not exists
        (
        select *
        from colsubtype
        where table1.coltype = colsubtype.coltype
            and table1.colsubtype = colsubtype.colsubtype
        ) 

Solution 2

Using NOT EXISTS:

delete from t1 
    from table1 t1
    where not exists (select null from coltype ct where ct.coltype = t1.coltype)
       or not exists (select null from colsubtype cst where cst.colsubtype = t1.colsubtype)

Using LEFT JOINs:

delete from t1 
    from table1 t1
        left join coltype ct
            on t1.coltype = ct.coltype
        left join colsubtype cst
            on t1.colsubtype = cst.colsubtype
    where ct.coltype is null 
       or cst.colsubtype is null

Comments

[Needs Help]

The above worked fine, except that it should also have deleted all "typ3" records too - which it did not.

[Jay Jay Jay]

Just found out/ was reminded that the first from is optional. Phew