0

I apologize in advance I feel like I'm missing something really stupid simple. (and let's ignore database structure as I'm kind of locked into that).

I have, let's use customer orders - an order number can be shipped to more than one place. For the sake of ease I'm just illustrating three but it could be more than that (home, office, gift, gift2, gift 3, etc)

So my table is:

Customer orders:

OrderID    MailingID
--------------------
1          1
1          2
1          3
2          1
3          1
3          3
4          1
4          2
4          3

What I need to find is OrderIDs that have been shipped to MailingID 1 but not 2 (basically what I need to find is orderID 2 and 3 above).

If it matters, I'm using Sql Express 2012.

Thanks

analias
  • 51
  • 5

2 Answers2

0

Maybe this could help:

create table #temp(
    orderID int,
    mailingID int   
)

insert into #temp
select 1, 1 union all
select 1, 2 union all
select 1, 3 union all
select 2, 1 union all
select 3, 1 union all
select 3, 3 union all
select 4, 1 union all
select 4, 2 union all
select 4, 3

-- find orderIDs that have been shipeed to mailingID = 1
select
    distinct orderID
from #temp
where mailingID = 1
except
-- find orderIDs that have been shipeed to mailingID = 2
select
    orderID
from #temp
where mailingID = 2

drop table #temp
Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
0

A simple Subquery With NOT IN Operator should work.

SELECT DISTINCT OrderID
FROM   <tablename> a
WHERE  orderid NOT IN (SELECT orderid
                       FROM   <tablename> b
                       WHERE  b.mailingID = 2)
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172