Restricting an SQL query to certain fields in a table not used by the query

Question

I have a products table, with the fields productname, category and cost, of type varchar, varchar and double.

I then have a sales table, with the fields productname, cost, and saledate, of type varchar, double and date.

Lastly, I have a purchases table with the fields purchase, cost and purchasedate, of type varchar, double and date.

I want to calculate the number of sales for certain dates, for certain categories.

I have at the moment this query to show the monthly totals for a year:

SELECT month(saledate), SUM(cost)
FROM sales
GROUP BY month(saledate)
UNION ALL
SELECT month(purchasedate), SUM(cost)
FROM purchases
GROUP BY month(purchasedate)

How could I restrict it to certain categories?

To define certain categories from products, such as food and drink, and only include products in these categories in my calculation?

Answer 1

just add a WHERE clause like this one:

[...] WHERE products.category = 'food' OR prodcuts.category = 'drink' [...]

Answer 2

SELECT month(saledate), SUM(sales.cost)
FROM sales, products
WHERE sales.productname=products.productname
AND category='food'
GROUP BY month(saledate)
UNION ALL
SELECT month(purchasedate), SUM(purchases.cost)
FROM purchases, products
WHERE purchases.productname=products.productname
AND category='food'
GROUP BY month(purchasedate)

The table needs to be part of the SQL statement in order to restrict the result-set rows based on some value held in it.

Answer 3

I'd be inclined to use a subquery rather than a join, and I think you want it in both parts of the union. But Borealid's answer is quite sound. The difference is a matter of taste.

SELECT month(saledate),
       SUM(sales.cost)
    FROM sales, products
    WHERE EXISTS (SELECT *
                  FROM products
                  WHERE purchases.productname = products.productname AND
                        category              = 'food')
    GROUP BY month(saledate)
UNION ALL
SELECT month(purchasedate),
       SUM(purchases.cost)
    FROM purchases, products
    WHERE EXISTS (SELECT *
                      FROM products
                      WHERE purchases.productname = products.productname AND
                            category              = 'food')
    GROUP BY month(purchasedate)

Answer 4

If you want your calculation result doesn't include these categories, you should use WHERE.

otherwise, using HAVING