Tutorial :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?

Solution:1

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.

Solution:2

just add a WHERE clause like this one:

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

Solution:3

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

otherwise, using HAVING

Solution:4

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)

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com