adding a where clause from a separate table

Question

I have a sales table, with fields product and cost of type varchar and decimal.

I also have a purchases table, with fields product and cost, also of type varchar and decimal.

lastly, I have a products table, with fields name, category and cost, of type varchar, varchar and decimal.

I am doing the following query to get the NET income

SELECT Q1.total_sales - Q2.total_purchases AS NET
  FROM (select SUM(sales.cost) AS total_sales
          from sales) Q1, 
       (select SUM(purchases.cost) AS total_purchases
          from purchases) Q2 

How can I relate it back to products in the products table, to only select certain products where category = blah?

Would this have to be a union?

I would also like to show this for the current year or month, to do so would I select the date field and just append

WHERE MONTH( s.saledate ) = MONTH( NOW( ) )

to my query?

Answer 1

Something like this...?

 SELECT Q1.total_sales - Q2.total_purchases AS NET
        FROM (select SUM(sales.cost) AS total_sales, product
        from sales) Q1, (select SUM(purchases.cost) AS total_purchases
        from purchases) Q2 
    INNER JOIN Products on Q1.Product = Products.Product
    WHERE Products.Category = "blah"

Answer 2

Use:

SELECT Q1.total_sales - Q2.total_purchases AS NET
  FROM (SELECT SUM(s.cost) AS total_sales
          FROM SALES s
          JOIN PRODUCTS p ON p.product = s.product
                         AND p.category = ?
         WHERE MONTH(s.saledate) = MONTH(NOW()) ) Q1, 
   (SELECT SUM(p.cost) AS total_purchases
      FROM PURCHASES p) Q2 

Answer 3

Add this to the end of your query

JOIN products AS p ON p.id = Q1.id WHERE p.category = @blah