View with Full Outer Join
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 that concatenates each post with its forecast (same user_id and same date). This is good, but I have a problem with those lines that have a forecast and no record, or vice versa.
I would like all records not to be contained in the database. They should appear with zero values in the forecast fields. And the same goes for predictions without records.
+2
source to share
2 answers
This command (FULL OUTER JOIN) is not available on MySql , you will need to use UNION to achieve this as 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
+2
source to share
What have you tried?
A full outer join will do this for you. You can simulate one with two outer joins and combine everything:
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
0
source to share