By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE COMP ([Item] varchar(2));
INSERT INTO COMP ([Item]) VALUES
('A2');
CREATE TABLE ITEMS ([Order] int, [Item] varchar(2));
INSERT INTO ITEMS ([Order], [Item]) VALUES
(1, 'A1'),
(1, 'A2'),
(2, 'A1')
;
4 rows affected
select i.[Order] from ITEMS i
where i.[Order] IN (
select [Order] from ITEMS
group by [Order]
having count(distinct [Item]) = 1
)
and not exists (select 1 from COMP where [Item] = i.[Item])
| Order |
|---|
| 2 |
select i.[Order]
from ITEMS i left join COMP c
on c.[Item] = i.[Item]
where i.[Order] IN (
select [Order] from ITEMS
group by [Order]
having count(distinct [Item]) = 1
)
and c.[Item] is null
| Order |
|---|
| 2 |