SQL query ignoring where clause?

Question

I have this query

SELECT theMonth, 
       sum( Sales ) AS sumSales, 
       sum( Saleswotax ) AS sumSaleswotax, 
       sum( Purchases ) AS sumPurchases, 
       sum( Purchaseswotax ) AS sumPurchaseswotax
FROM (SELECT date_format( saledate, '%M' ) AS theMonth, 
             sales.cost AS Sales, 
             ROUND( sales.cost * 0.85, 2 ) AS Saleswotax, 
             0 AS Purchases, 
             0 AS Purchaseswotax
        FROM sales, products
       WHERE sales.product = products.name
         AND category = 'Service'
          OR category = 'Accommodation'
      UNION ALL
      SELECT date_format( purchasedate, '%M' ) AS theMonth, 
             0 AS Sales, 
             0 AS Saleswotax, 
             purchases.cost AS Purchases, 
             ROUND( purchases.cost * 0.85, 2 ) AS Purchaseswotax
        FROM purchases) AS all_costs
GROUP BY theMonth

This is meant to show the total sales and purchases for the current year.

It works fine in that regard, but seems to ignore my category WHERE clause.

If I replace service with xxx and accommodation with jjj I still get the same result.

The category field is only in the products table, but changing category to products.category made no difference.

Why is it not giving an error, why is it simply ignoring the clause?

Answer 1

I suspect it is because you misused your boolean algebra.

You wrote

where x and y or z

But it is hard to tell if you meant x and (y or z) or (x and y) or z. I assume the first version, since that is the only one that seems logical. Try adding parens to make sure your logic is interpreted correctly.

Answer 2

Your where clause is essentially

(sales.product=products.name AND category='Service') OR category='Accommodation'

Perhaps you want

sales.product=products.name AND (category='Service' OR category='Accommodation')

Answer 3

You have two options:

1. Use brackets for proper iterpretation of the OR statement
2. Use an IN clause (best option)

IN clause example:

....
FROM (SELECT date_format( saledate, '%M' ) AS theMonth, 
             sales.cost AS Sales, 
             ROUND( sales.cost * 0.85, 2 ) AS Saleswotax, 
             0 AS Purchases, 
             0 AS Purchaseswotax
        FROM sales, products
       WHERE sales.product = products.name
         AND category IN ('Service', 'Accommodation')
     UNION ALL ...

Brackets example:

....
FROM (SELECT date_format( saledate, '%M' ) AS theMonth, 
             sales.cost AS Sales, 
             ROUND( sales.cost * 0.85, 2 ) AS Saleswotax, 
             0 AS Purchases, 
             0 AS Purchaseswotax
        FROM sales, products
       WHERE sales.product = products.name
         AND (category = 'Service' OR category = 'Accommodation')
     UNION ALL ...

Answer 4

Add parenthesis?

WHERE sales.product = products.name
AND (category = 'Service' OR category = 'Accommodation')