OPENEDGE (ODBC) Joining without Duplicates on ID

trying to query 3 tables without duplicates on the Object_ID

If this is not possible, order for both Object_ID, so the duplicates of both Object_IDs are among themselves, would also do the trick for me. But this is not really working for me, since Im only able to order by one Object_ID, so the duplicates are among themselves.

Tables:

S_Anl

Ktr Anl
4711 1234
4711 5678
4711 9000


AB_Erg

Anl AB_Erg_Obj Value
1234 c9d91f 1000
1234 696bfc 2000
1234 8c9915 3000
5678 141a65 4000


E_BP

Anl E_BP_Obj Value
1234 99f75ab 500
1234 720e573 100
9000 830614c 50
9000 958ac28 200


Query

SELECT B.AB_Erg_Obj, C.E_BP_Obj, A.Anl, B.Value, C.Value
FROM PUB.S_Anl AS A
LEFT JOIN PUB.AB_Erg AS B ON A.Anl = B.Anl
LEFT JOIN PUB.E_BP AS C ON A.Anl = C.Anl
WHERE A.Ktr = '4711'
ORDER BY A.Anl, B.AB_Erg_Obj, C.E_BP_Obj
with (nolock)


Expected Result

Anl AB_Erg_Obj E_BP_Obj Value Value
1234 c9d91f 99f75ab 1000 500
1234 696bfc 720e573 2000 100
1234 8c9915 NULL 3000 NULL
5678 141a65 NULL 4000 NULL
9000 830614c 830614c NULL 50
9000 958ac28 958ac28 NULL 200


Or Ordering AB_Erg_Obj and E_BP_Obj among themselves. Is either of this possible?

//EDIT:

I know that ordering wouldnt remove duplicates in the result set, but it would be easier to do it afterwards.

Also its not necassary that the data is matched exactly on row-level, I just need the overall sum of Value from E_BP and Value of AB_Erg in the first place - because of that exact matching on row-level is not needed, just no duplicates on the Object_ID-Level

Continue reading...