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!

• ypercubeᵀᴹ: Is it product.id or product.id_product? The code does not match the description. And why don't you have the same (product_id) name for the column everywhere?

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

• Larissa Leite: Thanks, ypercube, apparently it works. The only thing is that on the result, when I set VALUES (1),(2),(3),(4),(5),(6) for months, there are rows even for the months when there is no correspondent receipts or sales registered. How can I avoid that? In fact, could you explain a little more why the CROSS JOIN is necessary?
• Larissa Leite: Worked just fine :) Thank you very much!!!