SELECT OrderNo, Item, Status
FROM Table1 t1
WHERE 
	Status = 20 AND 
	OrderNo NOT IN
		(
			SELECT OrderNo
			FROM Table1
			WHERE Status = 10
		)

declare @t table(OrderNo int, Item varchar(10),Status int)
insert @t 
select 1010,'Paper',20 union
select 1010,'Pencil',20 union
select 1010,'Staple',20 union
select 1011,'Paper',20 union
select 1011,'Brusch',20 union
select 1011,'Bag',10 union
select 1012,'Paper',19 union
select 1012,'Bag',19 union
select 1012,'Staple',19 
select 1013,'Paper',18 union
select 1013,'Bag',18 union
select 1013,'Staple',18 

select OrderNo,min(status) Status
from @t t
group by OrderNo having min(t.status)=max(t.status) and 
min(status) in (19,20)

quote:

---

Originally posted by henrikf

Thanks!

This workes out just fine if I'm only interested in one status. But, lets say that I'm interested in finding orders with status 19 and 20 and not the other orders, for which the statuses are unknown (lets asume they can have over 1000 different statuses and I'm unwilling to type them in the NOT IN part of the script...

//Henrik

---

select
	t1.OrderNo,
	t1.Item,
	t1.Status
from
	Table1 t1
where 
	t1.Status in ( 19, 20 )	and 
	t1.OrderNo not in
		(
		select
			t2.OrderNo
		from
			Table1 t2
		where
			t2.OrderNo = t1.OrderNo
			t2.Status not in ( 19, 20 )
		)