MYSQL left join missing out rows

Question

I have 4 tables.

1st table has: date and revenue
2nd table has: date and cost
3rd table has: date and fees 
4th table has: date and others

I am calculating final revenue values using the formula:

final = revenue - (cost + fees + others)

To perform this I am using a coalesce operation while doing a left outer join on all these 4 tables. Since order of joins matter in left outer, I am missing out fees when there's no revenue or cost. Join order is revenue => cost => fees => others tables on date.

How can I display these missing rows on missing dates if the revenue / cost is missing but fees/ others are there?

Answer 1

I think that you need in this:

SELECT `date`,  COALESCE(t1.revenue, 0) 
              - COALESCE(t2.cost, 0) 
              - COALESCE(t3.fees, 0) 
              - COALESCE(t4.others, 0) final
FROM ( SELECT `date` FROM t1
       UNION 
       SELECT `date` FROM t2
       UNION 
       SELECT `date` FROM t3
       UNION 
       SELECT `date` FROM t4 ) dates
LEFT JOIN t1 USING (`date`)
LEFT JOIN t2 USING (`date`)
LEFT JOIN t3 USING (`date`)
LEFT JOIN t4 USING (`date`)

If date column is not defined as unique in each table (client-side check is not enough!) then you must add GROUP BY and use SUM() over each money (?) column in output expression.