Excluding records where criteria meets

Question

I have a set of records where we identify several items connected to a customer. My dilemma is that if a customer has both items then I would like to exclude that customer.

If they only have one specific item then I want to include it. I will be using this code to create a view so i'm trying to find the best way. I could try Row_number() to identify different records, but I'm not sure that would be ideal in this situation.

Example data table:

Customer | ItemID | value1 | Value2
   A         12       35       0
   B         12       35       0
   C         13       0        25
   C         12       0        25
   D         18       225       12

Desired Output:

 Customer | ItemID | value1 | Value2
   A         12       35       0
   B         12       35       0

This is what I have so far:

select Customer, ItemID, Value1, Value2
from Table1 
where itemID = 12

This would give me customer 'C', which I don't want.

Answer 1

If you want customers who have itemid = 12 but not itemid = 13 you can use NOT EXISTS:

select * from tablename t
where itemid = 12
and not exists (
  select 1 from tablename
  where customer = t.customer
  and itemid = 13
)

If you want customers who have itemid = 12 and not any other itemid:

select * from tablename t
where itemid = 12
and not exists (
  select 1 from tablename
  where customer = t.customer
  and itemid <> 12
)

or:

select * from tablename
where customer in (
  select customer from tablename
  group by customer
  having min(itemid) = 12 and max(itemid) = 12 
)