Не получается правильно объединить таблицы, что не так делаю?

Question

[Djasar]

Таблицы в ORACLE:


Написал такой код, но не выходит:

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;

Что делаю не так?

Comments

[Djasar]

Павел Ерошевич спасибо натолкнул на мысль

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 1

[Djasar]

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

[Павел Ерошевич]

может быть так?

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

Comments

[Павел Ерошевич]

Или если есть записи с ZIP и RAR на один MOID, то

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