View with Full Outer Join

Question

I want to create a view from the following two tables:

Entry:
entry_id
entry_date
entry_amount
user_id

Forecast:
forecast_id
forecast_date
forecast_amount
user_id

Now I want to have a view which combines each entry with its forecast (same user_id and same date). This is fine, but I have a problem with those rows, which have a forecast and no entry, or vice versa.

I would like to still have all entries with no forecasts as well in the database. They should show up with a null values in the forecast fields. And the same for forecasts without entries.

Thx, Martin

Answer 1

What have you tried?

A full outer join would do that for you. You can simulate one with two outer joins and a union all:

select
   e.entry_id, e.entry_date, e.entry_amount, e.user_id,
   f.forecast_id, f.forecast_date, f.forecast_amount, f.user_id
from
   Entry e
   left outer join Forecast f on f.user_id = e.user_id and f.forecast_date = e.entry_date
union all
select
   e.entry_id, e.entry_date, e.entry_amount, e.user_id,
   f.forecast_id, f.forecast_date, f.forecast_amount, f.user_id
from
   Entry e
   right outer join Forecast f on f.user_id = e.user_id and f.forecast_date = e.entry_date
where
   e.entry_id is null

Answer 2

This (FULL OUTER JOIN) command is not available on MySql you will need to use UNION to achieve this as given below

SELECT E.*, F.*
 FROM Entry E
 LEFT JOIN Forecast F ON E.user_id = F.user_id AND E.entry_date = F.forecast_date
UNION ALL
SELECT E.*, F.*
 FROM Forecast F 
 LEFT JOIN Entry E ON E.user_id = F.user_id AND E.entry_date = F.forecast_date