Duplicates and Ranking

[dechrist]

I have three tables I'm trying to join together by ID. TableA has duplicates based on Col2 and Col3. TableB has multiple entries per ID which I need to sum.

TableA
ID Col2 Col3
1 123 99999
2 123 99999
3 abc 11111
4 abc 11111

TableB
ID Col4
1 30
2 20
2 100
3 50
4 25

TableC
ID Col5
1 a1b2
3 y9z0


Desired Output
ID Col2 Col3 Col4Sum Col5
2 123 99999 150 a1b2
3 abc 11111 75 y9z0

The first record is generated by combining IDs 1 and 2. Using Col2 and Col3 from ID 2, because the sum of Col4 is more for ID 2 (120) than ID 1 (30). Summing Col4 for both IDs 1 and 2. And using Col5 from ID 1.

Thanks in advance for the help.