Get IDs that have a one specific value but do not have another specific value

I have开发者_Python百科 two columns

OrderId  OrderStatus
120      1
120      2
121      1
123      1
123      2

I want to retrieve only the row with OrderId 121 because it has had an OrderStatus of 1 but it does not have an OrderStatus of 2.

Because you don't mention there being a 'StatusDate' or anything like that, I am going to take

I want to only bring back the row with OrderId 121 because it has had an OrderStatus of 1 BUT IT HAS NEVER HAD AN OrderStatus of 2.

to mean

I want to only bring back OrderId 121, because there is a row with OrderId 121 and OrderStatus 1, but there is no row with OrderId 121 and OrderStatus 2

With SQL 2005 and later, EXCEPT makes this extremely concise:

SELECT OrderId FROM [Order] WHERE OrderStatus = 1
EXCEPT
SELECT OrderId FROM [Order] WHERE OrderStatus = 2

EXCEPT returns distinct values so there is no need for any further DISTINCT.

You can use a self-join, searching for OrderStatus = 1 from the left and missed join with OrderStatus = 2 from the right:

declare @t table(OrderID int, OrderStatus int)
insert into @t values (120, 1)
insert into @t values (120, 2)
insert into @t values (121, 1)
insert into @t values (123, 1)
insert into @t values (123, 2)

select t1.* 
from @t 
    t1 left join 
    (select * from @t where OrderStatus = 2) as t2 on t2.OrderID = t1.OrderID 
where 
    t1.OrderStatus = 1 and
    t2.OrderID is null

If your order statuses are only 1 and 2, and the orders must be a status of 1 at some point before becoming a status of 2, you can search for the orders with a maximum order status value of 1:

  select distinct orderid from orders
group by orderid
  having max(orderstatus) = 1;

Demo: http://www.sqlize.com/2k3C2SqMH2

Or if it's not quite that simple, we can be more explicit about not allowing an orderstatus of 2 to ever have happened by using a not exists clause:

select distinct orderid
  from orders o
 where not exists (
     select * from orders o2
     where o2.orderid = o.orderid
       and o2.orderstatus = 2
   );

Demo: http://www.sqlize.com/o6fSvWmvaj