How to INNER JOIN and OUTER JOIN within the same table?

Question

I am using PostgreSQL and I have the following tables:

1. Product

   ID | Name | Price
   

2. Receipts

   ID | Month | Quantity | Product_ID (FK)
   

3. Sales

   ID | Month | Quantity | Product_ID (FK)
   

I am trying to come up with a query that displays something like this:

    Month | Product.Name | Amount received | Amount sold

It is very important to keep in mind that there can be more than one receipt or sale of the same product on the same month. I need to come up with a query that tells me the amount received and the amount sold of a particular product on a each month. It is possible that a product is not sold in a month (that's why the OUTER JOIN below).

The best that I've got so far is this:

    SELECT s.month, p.name, r.quantity, SUM(s.quantity)
    FROM product AS p
    INNER JOIN receipts AS r
       ON p.id_product = r.product_id
    LEFT OUTER JOIN sales AS s
       ON p.id_product = v.product_id 
    GROUP BY s.month, p.name, r.quantity

Let's say in the same month there were 2 receipts of keyboards. The first one with 100 keyboards and the second one with 50 keyboards. Also, there was a sale of 10 keyboards and then another one of 20 keyboards.

An example of what is returned by this query:

     Month | Product.Name | Amount received | Amount sold
       6   |   Keyboard   |       100       |    30
       6   |   Keyboard   |       50        |    30     

This one works fine when there are mutiple sales (apart from the repetition) but not when there are multiple receipts. I was thinking that I should relate s.month and r.month with an INNER JOIN, but I don't know how.

Sorry for the bunch of information at once. If there is anything unclear, let me know. Can anyone help me out?

Thanks!

Answer 1

The query is joining 2 tables (receipts, sales) that both have a many-to-one relationship with product. This creates a kind of cartesian (cross) product and will give wrong results in the SUM() calculations. To avoid that, you need to do the summations for the two tables in two different subqueries to avoid errors.

Something like this will work:

SELECT 
    m.month, 
    p.name, 
    COALESCE(r.receipts_quantity, 0) AS receipts_quantity, 
    COALESCE(s.sales_quantity, 0)    AS sales_quantity
FROM 
    product AS p
  CROSS JOIN
    ( VALUES (1),(2),(3),(4),(5),(6) ) AS m(month)     -- months wanted in the report
  LEFT OUTER JOIN 
    ( SELECT month, product_id, SUM(quantity) AS receipts_quantity
      FROM receipts
      GROUP BY month, product_id
    ) AS r
    ON  p.id_product = r.product_id
    AND m.month = r.month
  LEFT OUTER JOIN  
    ( SELECT month, product_id, SUM(quantity) AS sales_quantity
      FROM sales
      GROUP BY month, product_id
    ) AS s
    ON  p.id_product = s.product_id
    AND m.month = s.month ;

If you want to avoid having results for products that did not have any activity in a month, you can add the condition:

WHERE r.month IS NOT NULL 
   OR s.month IS NOT NULL