conditional statement in sql server 2k5

Question

How would I filter this query to NOT show any items where "discontinued =1" and quantity=0"? I don't want to exclude all items with a value of discontinued =1. This only applies if the item has the value of discontinued=1 and the quantity =0.

  select sku,producttitle,location,pt_type as type, vendor_name, 
  active,pricestatus,  disc as discontinued,season,yourprice as  
  customerprice,costprice,quantity,stockvalue 

  from getskus

Answer 1

WHERE NOT (discontinued = 1 AND quantity = 0)

This represents exactly what you want to express. You exclude (keyword NOT) rows satisfying both (keyword AND) your desired conditions.

Answer 2

SELECT sku,
      producttitle,
      location,
      pt_type AS TYPE,
      vendor_name,
      ACTIVE,
      pricestatus,
      disc AS discontinued,
      season,
      yourprice AS customerprice,
      costprice,
      quantity,
      stockvalue
FROM  getskus
WHERE NOT (disc = 1 AND quantity = 0)

you can also change direction like this

SELECT sku,
       producttitle,
       location,
       pt_type AS TYPE,
       vendor_name,
       ACTIVE,
       pricestatus,
       disc AS discontinued,
       season,
       yourprice AS customerprice,
       costprice,
       quantity,
       stockvalue
FROM   getskus
WHERE  disc <> 1
       OR  quantity <> 0

But I would prefer the first one

Answer 3

select sku
       ,producttitle
       ,location
       ,pt_type as type
       ,vendor_name
       ,active
       ,pricestatus
       ,disc as discontinued
       ,season
       ,yourprice as customerprice
       ,costprice
       ,quantity
       ,stockvalue  
from getskus 
WHERE NOT (disc = 1 and quantity= 0)