CODEWITHSUNDEEP
sqlt-sqlsql-server-2005
wondergoat77
wondergoat77

Reputation: 1815

SQL - don't display row when two criteria are met

At first I thought I could accomplish this in the WHERE clause of my query and using a CASE (see below) but I'm having some trouble getting the result I need. This query is to get product info for clients in a database. I need to not return rows at all if a certain product is ordered by a certain client. The problem is if I remove the product, then any other client with that product will show up as any client can order this product. I'm stumped on how to accomplish this.

Select
   OrderNumber, Client, Product From Orders
WHERE
  Product not in(CASE WHEN client = 'client_a' and product = 'product_1' THEN Product   END)

Hoping to see something like this as a result, the point being to show product_1 for all clients except client_a:

ordernumber client          product
123         client a    product 2
456         client b    product 1
789         client b    product 2

Upvotes: 1

Views: 9619

Answers (4)

GarethD
GarethD

Reputation: 69769

This is because of the way NOT IN works. If any of the values in the list are NULL then it will return no results. e.g.

SELECT  [TestColumn] = 3
WHERE   3 NOT IN (1, 2, NULL)

Will return no results, and not 3.

If you expand the example into the equivalent OR statement you will see why:

WHERE   3 != 1
AND     3 != 2
AND     3 != NULL

Since 3 != NULL Evaluates to false since nothing equals NULL the statement returns false.

You can accomplish the required result in 3 ways.

1) Use ELSE in your case statement to ensure no NULL results

SELECT  OrderNumber, Client, Product
FROM    Orders
WHERE    Product not in(CASE WHEN client = 'client_a' and product = 'product_1' THEN Product ELSE '' END)

2) Change IN to not equal, since your case statement will only return 1 value anyway

SELECT  OrderNumber, Client, Product 
FROM    Orders
WHERE   Product != (CASE WHEN client = 'client_a' and product = 'product_1' THEN Product END)

3) Revise your logic, this can be rewritten as

SELECT  OrderNumber, Client, Product 
FROM    Orders
WHERE   NOT (Product = 'product_1' AND Client = 'Client_a')

Upvotes: 2

Dave Sexton
Dave Sexton

Reputation: 11188

I think you over complicating something very simple, don't you just want:

SELECT
   OrderNumber, Client, Product 
FROM Orders
WHERE Product != 'product_1' 
  AND client = 'client_a'

Upvotes: 2

bonCodigo
bonCodigo

Reputation: 14361

Try this :

SELECT OrderNumber, Client, Product
FROM Orders
WHERE Client != 'client_a' 
OR Product != 'product_1'
;

Upvotes: 1

lc.
lc.

Reputation: 116498

SELECT OrderNumber, Client, Product
FROM Orders
WHERE Client <> 'client_a' OR Product <> 'product_1'

Or, semantically equivalent and maybe a little easier to see why this works:

SELECT OrderNumber, Client, Product
FROM Orders
WHERE NOT (Client = 'client_a' AND Product = 'product_1')

Upvotes: 1

Related Questions