I have 3 tables of purchases in the following format:

1. date | company_id | apple_txn_amt

2. date | company_id | orange_txn_amt

3. date | company_id | pear_txn_amt

There are multiple purchases/sales daily for many companies. I'm trying to join and group so there is only 1 date per company along with total fruit balance:

date | company_id | total_apple_balance | total_apple_orange_balance | total_pear_balance

I have built a query for a similar case earlier, and used 2 joins. But this was for only one company's data so I was only joining on date=date for each table. Process for each table was: gather buys, sells, union those two, union to a new table with generate_series() to insert 0s for days missing, calculate daily delta, and group by day to have a running total. Then something like:

SELECT 
  apple.day
  apple.total
  orange.total
  pear.total
  (apple   orange   pear) AS total_fruit
FROM apple
JOIN orange ON orange.date = apple.date
JOIN pear ON pear.date = apple.date
ORDER BY day

It's like I need to JOIN ON date and company id but from what I can tell this isn't possible.

Should I approach this in a different way?

CodePudding user response：

Sure you can add the company_id like

SELECT 
  apple.day
  apple.total
  orange.total
  pear.total
  (apple   orange   pear) AS total_fruit
FROM apple
JOIN orange ON orange.date = apple.date AND orange.company_id = apple.company_id 
JOIN pear ON pear.date = apple.date AND pear.company_id = apple.company_id 
ORDER BY day

But the design of your database isn't right, if circumstances don't require it.

you would not have 3 tables, you would have only one with Fruit type as another column, to differentiate them