Bartonb12 Bartonb12 - 4 months ago 9
SQL Question

My where clause is returning the opposite of what I want

This is my query.

select sp.name, spi.sku, sum(spi.price), count(sp.name), spi.in_stock, spi.price, sp.is_published, spi.is_reorderable
from shop_product_category spc
inner join shop_product_category_xref spcx
on spc.shop_product_category_id = spcx.shop_product_category_id
inner join shop_product sp
on sp.shop_product_id = spcx.shop_product_id
inner join shop_product_item spi
on spi.shop_product_id = sp.shop_product_id
inner join shop_order_item soi
on spi.shop_product_item_id = soi.shop_product_item_id
where (spc.shop_product_category_id in (1316))
and sp.is_published = 1
group by sp.name
order by count(sp.name) desc


It returns everything just fine EXCEPT I want it to return lines where sp.is_published = 1 Hence the where clause. Yet to get the desired output, I have to change the current where clause to
sp.is_published <> 1

So the questions is this, why does <> 1 return rows with ones, and =1 return rows with zeros. Thanks

Zak Zak
Answer

If sp.is_published = 1 is a string, you'll need to put it in ticks IE sp.is_published = '1'