[SOLVED] Check if a group contains all items from another table

Check if a group contains all items from another table

I want to check if Sequence is complete at Sequence index.

I have a table @workREQUIREMENTS like this that has the required/planned INTRAWORKNO for a sequence:

INTRAWORKNO
10
20
30

and I have a table @results like this:

SEQUENCE	INTRAWORKNO	part
1	10	0
1	30	0
2	10	0
2	20	0
2	30	0
3	10	0
3	10	1
3	10	2
6	40	0
7	40	0
8	10	0
8	20	0
8	20	1
8	30	0

How can I delete this SEQUENCEs, where I did not have ALL INTRAWORKNO in the corresponding SEQUENCE?

As you see,

sequence 1 has no INTRAWORKNO 20, which eleminates this sequence --> NG
sequence 2 has ALL INTRAWORKNO --> OK
sequence 3 has no INTRAWORKNO 20 and no 30 --> NG
sequence 6 and 7 have no values from the @workREQUIREMENTS-table --> NG
sequence 8 has ALL INTRAWORKNO (Intraworkno 20 is divided in sub-parts) --> OK

A group by clause with a count brings not the expected results, because there can be multiple sub-parts (characteristics) of these SEQUENCE

The desired results are in bold.

Solution

The looks like relational division. You can use double not exists for this (explanation here):

select *
from results as r1
where not exists (
    select *
    from requirements as reqd
    where not exists (
        select *
        from results as r2
        where r1.sequence = r2.sequence and r2.intraworkno = reqd.intraworkno
    )
);

DB<>Fiddle