SQL: Join four tables without duplicates and losing data

I want to join four tables, without losing data and without duplicates.

Im using ODBC on a Progress-OpenEdge-Database(FULL OUTER JOIN is not working).

TableA


AnlNr
-------
Anl1234
Anl5678
Anl9000
Anl8000


TableB


Ref_AnlNr Value_bil
----------------------
Anl1234 100
Anl1234 200
Anl5678 50
Anl8000 10


TableC


Ref_AnlNr Ref_ins
-------------------
Anl1234 Ref1
Anl1234 Ref1
Anl1234 Ref2
Anl9000 Ref3


TableD


Ref_ins Value_in
--------------------
Ref1 140
Ref2 100
Ref3 500


Result:


Anl1234 100 140
Anl1234 200 100
Anl5678 50 NULL
Anl9000 NULL 500
Anl8000 10 NULL


What is the best way of doing this in one query?

Continue reading...