Can't merge tables correctly, what am I doing wrong?

Question

Oracle

Tables in ORACLE:


I wrote the following code, but it doesn't work:

Select m.MOID, a.DATE, m.NAME, a.RAR, a2.ZIP
FROM (SELECT * FROM (SELECT MOID, RAR, DATE FROM MDA)
UNION ALL 
SELECT * FROM (SELECT MOID, RAR, DATE FROM MDA2))a
LEFT JOIN
(SELECT * FROM (SELECT MOID, ZIP, DATE FROM SAS)
UNION ALL 
SELECT * FROM (SELECT MOID, ZIP, DATE FROM SAS2))a2
LEFT JOIN ORIGINAL m ON m.MOID = a.MOID and m.MOID = a2.MOID;

What am I doing wrong?

Answer 1

Select m.MOID, a.DATE, m.NAME, a.RAR, a.ZIP
FROM (SELECT * FROM (SELECT MOID, RAR,null ZIP, DATE FROM MDA
          UNION ALL 
          SELECT MOID, RAR,null ZIP, DATE FROM MDA2)
          UNION ALL
          (SELECT MOID,null RAR, ZIP, DATE FROM SAS
          UNION ALL 
          SELECT MOID,null  RAR, ZIP, DATE FROM SAS2))a
LEFT JOIN ORIGINAL m ON m.MOID = a.MOID;

Answer 2

maybe so?

select *
  from (select t.MOID, m.DDATE, m.RAR, null ZIP
          from Original t,
               (select * from MDA m1 UNION select * from MDA2 m2) m
         where m.MOID = t.MOID
        UNION ALL
        select t.MOID, s.DDATE, null RAR, s.ZIP ZIP
          from Original t,
               (select * from SAS s1 UNION select * from SAS2 s2) s
         where s.MOID = t.MOID)
 ORDER BY MOID