CODEWITHSUNDEEP
sqlsql-serverrow-number
WWaldo
WWaldo

Reputation: 223

Excluding records where criteria meets

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.

Upvotes: 1

Views: 53

Answers (2)

CGul
CGul

Reputation: 167

I think you need to clarify your question but, as I understand it, you're looking to return the all rows where:

1) A customer has a particular item (i.e. Item ID 12, which excludes customer D)

and

(2) They only have one item in total, which excludes customer C since they have two items.

If that is the case, then here's what I've got:

SELECT * 
FROM Table1
WHERE ItemID == '12' AND 
      Customer in (
                   SELECT Customer
                   FROM Table1
                   GROUP BY Customer
                   HAVING Count(Customer) = 1
                  )

Edit: I clarified my interpretation of OP's question. I also tested my solution on SQL Fiddle (http://sqlfiddle.com/#!5/b5f1f/2/0) and updated the WHERE clause accordingly.

Upvotes: 1

forpas
forpas

Reputation: 164099

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 
)

Upvotes: 1

Related Questions