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

Question

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

basically the same as above but add a line that shows client a with product 1 — wondergoat77, Jan 16 '13 at 17:47

score 2 · Answer 1 · answered Jan 16 '13 at 17:38

2

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'

answered Jan 16 '13 at 17:38

Dave Sexton

10,768
3
42
56

GarethD · Accepted Answer · 2013-01-16T17:45:31.577

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')

i see the logic behind all of this, but its not working for my query. obviously i posted a generic query so its probably something in my query. ill see if i can work it out, otherwise ill post an edit — wondergoat77, Jan 16 '13 at 17:48
nevermind, was a number off by 1 digit in my query, this worked wonders, thanks! — wondergoat77, Jan 16 '13 at 17:50

score 1 · Answer 3 · answered Jan 16 '13 at 17:35

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')

score 1 · Answer 4 · answered Jan 16 '13 at 17:37

1

Try this :

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

answered Jan 16 '13 at 17:37

bonCodigo

14,268
1
48
91

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

4 Answers4

Linked