CODEWITHSUNDEEP
sql-server
Bernard Polman
Bernard Polman

Reputation: 855

WHERE clause condition when a column has a specific value

I'm facing a problem in WHERE clause in my query:

SELECT
p.productId,
p.productType,
p.quantity_for_customers,
p.quantity_for_sellers,

FROM dbb.Products


WHERE (p.productType IS NOT NULL 
      OR 
      (p.productType = 1 AND p.quantity_for_customers > 0 
          AND p.quantity_for_sellers > 0)
      )

I want to select product information, but if the product has type = 1, then I want to select that product if both quantities (customers and sellers) are greater than zero.

The WHERE clause I wrote does not work because some products have type = 1, but one for their quantites is = 0, but they still show up in query results.

What's wrong with the WHERE clause?

Upvotes: 0

Views: 99

Answers (4)

forpas
forpas

Reputation: 164089

Your condition can be simplified to this:

WHERE p.productType <> 1
      OR 
      (p.productType is not null and p.quantity_for_customers > 0 AND p.quantity_for_sellers > 0)

See a simplified demo.

Upvotes: 0

simon at rcl
simon at rcl

Reputation: 7344

I've edited the display of the WHERE clause in your question to show the parts more clearly.

It will show a product is:

  • productType is not null

OR

  • productType is 1 AND both quantities are > 0

So anything with a productType which is not null will be selected.

Upvotes: 0

quorti
quorti

Reputation: 320

They show up, because the procutType is not null. Combined with the OR every entry whill show up that statisfies the condition productType IS NOT NULL.

To achieve what you want try this: 

WHERE p.productType IS NOT NULL AND
  (p.productType <> 1
  OR 
  (p.productType = 1 AND p.quantity_for_customers > 0 
      AND p.quantity_for_sellers > 0)
  )

Upvotes: 0

juergen d
juergen d

Reputation: 204756

WHERE p.productType <> 1
   OR (p.productType = 1 AND p.quantity_for_customers > 0 AND p.quantity_for_sellers > 0)

Upvotes: 3

Related Questions