SQL Server Check if a group contains all items from another table

rdlzhqv9 于 11个月前发布在其他

关注(0)|答案(1)|浏览(109)

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.

sql-server

来源：https://stackoverflow.com/questions/77748213/check-if-a-group-contains-all-items-from-another-table

1条答案

按热度按时间

js4nwp541#

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

赞(0）回复(0）举报 11个月前

我来回答

SQL Server Check if a group contains all items from another table

1条答案

相关问题

热门标签

最新问答