Cannot filter results set in SQL Server query

Question

For some reason I cannot get my results set to restrict products that only have the price status of "normal'. When I use a where clause like where pricestatus like 'normal' the results set also filters other records. Any ideas here?

SELECT   
   od.order_id, 
   ISNULL(p.pricestatus,'normal') as pricestatus,
   ISNULL(od.partnumber,'unknown') as partnumber,
   od.product_id, 
   od.producttitle, 
   qty, 
   od.price, 
   extprice, 
   customfield1 AS prodstatus, 
   ISNULL(categorytitle,'-') AS categorytitle, 
   needbydate, 
   customfield2 AS vendor, 
   (SELECT opttitle 
      FROM options o 
     WHERE Charindex(',' + Cast(o.opt_id AS VARCHAR) + ',',',' + opt_ids + ',') > 0 
           AND optlevel = 1) AS color, 
   (SELECT opttitle 
      FROM options o 
     WHERE Charindex(',' + Cast(o.opt_id AS VARCHAR) + ',',',' + opt_ids + ',') > 0 
           AND optlevel = 2) AS size, 
   od.producttitle + ISNULL((SELECT opttitle 
                               FROM options o 
                              WHERE Charindex(',' + Cast(o.opt_id AS VARCHAR) + ',',',' + opt_ids + ',') > 0 
                                AND optlevel = 1),'') + ISNULL((SELECT opttitle 
                                                                  FROM options o 
                                                                 WHERE  Charindex(',' + Cast(o.opt_id AS VARCHAR) + ',',',' + opt_ids + ',') > 0 
                                                                   AND optlevel = 2),'') + Cast(od.price AS VARCHAR(15)) AS pkey

FROM     
   orderdetails od 
  INNER JOIN orders ord ON ord.order_id = od.order_id 
  LEFT OUTER JOIN products p ON p.product_id = od.product_id 
  LEFT OUTER JOIN objectgroups ob ON p.objectgroup_id = ob.objectgroup_id 
  LEFT OUTER JOIN categories c ON ob.category_id = c.category_id 
WHERE  
    1=1 
    AND ord.shippingdate BETWEEN {ts '2009-10-05 00:01:00'} AND {ts '2009-10-21 23:59:59'} 
    AND ISNULL(ord.status,0) IN (5,12) 
    AND ISNULL(ord.status,0) <> 6 
    AND ISNULL(ord.status,0) <> 10 
    AND ISNULL(ord.status,0) <> 7 
    AND ISNULL(ord.status,0) <> 8 
ORDER BY 
    pkey

Answer 1

What does the data look like? Unless the data is exactly the text Normal, you will not pick up variations of Normal.

Try the following instead

WHERE priceStatus LIKE "%normal%'

if you want NULL records as well, use

   WHERE PriceStatus LIKE '%normal%' or PriceStatus is NULL

Answer 2

I think you need to add this to the end of your where clause

and isnull(p.pricestatus,'normal')  = 'normal'

like this

SELECT   od.order_id, 
 Isnull(p.pricestatus,'normal') as pricestatus,
      Isnull(od.partnumber,'unknown') as partnumber,

     od.product_id, 
     od.producttitle, 
     qty, 
     od.price, 
     extprice, 
     customfield1                                                                                                                  AS prodstatus, 
     Isnull(categorytitle,'-')                                                                                                     AS categorytitle, 
     needbydate, 
     customfield2                                                                                                                  AS vendor, 
     (SELECT opttitle 
      FROM   options o 
      WHERE  Charindex(',' + Cast(o.opt_id AS VARCHAR) + ',',',' + opt_ids + ',') > 0 
             AND optlevel = 1) AS color, 
     (SELECT opttitle 
      FROM   options o 
      WHERE  Charindex(',' + Cast(o.opt_id AS VARCHAR) + ',',',' + opt_ids + ',') > 0 
             AND optlevel = 2) AS size, 
     od.producttitle + Isnull((SELECT opttitle 
                               FROM   options o 
                               WHERE  Charindex(',' + Cast(o.opt_id AS VARCHAR) + ',',',' + opt_ids + ',') > 0 
                                      AND optlevel = 1),'') + Isnull((SELECT opttitle 
                                                                      FROM   options o 
                                                                      WHERE  Charindex(',' + Cast(o.opt_id AS VARCHAR) + ',',',' + opt_ids + ',') > 0 
                                                                             AND optlevel = 2),'') + Cast(od.price AS VARCHAR(15)) AS pkey 


FROM     orderdetails od 
     INNER JOIN orders ord 
       ON ord.order_id = od.order_id 
     LEFT OUTER JOIN products p 
       ON p.product_id = od.product_id 



     LEFT OUTER JOIN objectgroups ob 
       ON p.objectgroup_id = ob.objectgroup_id 
     LEFT OUTER JOIN categories c 
       ON ob.category_id = c.category_id 
 WHERE  1=1 



  and isnull(p.pricestatus,'normal')  = 'normal'
     AND ord.shippingdate BETWEEN {ts '2009-10-05 00:01:00'} AND {ts '2009-10-21 23:59:59'} 
     AND Isnull(ord.status,0) IN (5,12) 
     AND Isnull(ord.status,0) <> 6 
     AND Isnull(ord.status,0) <> 10 
     AND Isnull(ord.status,0) <> 7 
     AND Isnull(ord.status,0) <> 8 


ORDER BY pkey

Answer 3

You are doing a LEFT OUTER JOIN on Products. Remeber that if you put a condition in the WHERE clause it's going to apply that condition to the WHOLE set, so any row that doesn't bring back a product will automatically get filtered out because your condition can't be true.

You will need to add the condition to your join instead:

LEFT OUTER JOIN products p 
   ON p.product_id = od.product_id AND p.priceStatus LIKE "%normal%'