How to combine 3 tables with have max value in other table

Question

I have 3 tables below:

Table 1:              Table 2:                     Table 3:
+------+-----+----+   +------+-----+----+-------+  +------+-----+----+---+-----+
| a    | b   | c  |   | a    | b   | c  | d     |  | a    | b   | c  | e | f   |
+------+-----+----+   +------+-----+----+-------+  +------+-----+----+---+-----+
| 1000 |  10 |  0 |   | 1000 |  10 |  0 |  5000 |  | 1000 |  10 |  0 | 3 |  40 | 
| 1000 |  20 |  1 |   | 1000 |  10 |  0 | 10000 |  | 1000 |  10 |  0 | 6 |  80 |
| 1000 | 100 |  1 |   | 1000 |  20 |  1 |  7500 |  | 1000 |  20 |  1 | 5 |  30 |  
| 1000 | 100 |  0 |   | 1000 | 100 |  0 |  1000 |  | 1000 |  20 |  1 | 6 |  70 |  
| 1000 | 100 |  2 |   | 1000 | 100 |  0 |  4000 |  | 1000 |  20 |  1 | 8 | 100 | 
| 2000 | 200 | 10 |   | 1000 | 100 |  1 | 10000 |  | 1000 | 100 |  0 | 2 |  20 |
                      | 1000 | 100 |  2 |  5000 |  | 1000 | 100 |  0 | 7 |  70 |
                      | 1000 | 100 |  2 | 20000 |  | 1000 | 100 |  1 | 2 |  20 |
                      | 2000 | 200 | 10 |  1000 |  | 1000 | 100 |  1 | 6 |  60 |
                      | 2000 | 200 | 10 |  4000 |  | 1000 | 100 |  2 | 2 |  20 |
                      | 2000 | 200 | 10 | 20000 |  | 1000 | 100 |  2 | 6 |  80 |
                                                   | 1000 | 100 |  2 | 8 |  90 |
                                                   | 2000 | 200 | 10 | 6 |  80 |

Expected result is like this:
Column (b) should be unique
Column (c) is the max value from a and b
Column (d) is the sum if got the max value of c (if there are 0,1,2 in c for a & b, then only need get value 2) discard the value 0 and 1
Column (e) and (f) also the same. only need to get the max value for data a and b

+------+-----+----+-------+---+-----+
| a    | b   | c  | d     | e | f   |
+------+-----+----+-------+---+-----+
| 1000 |  10 |  0 | 15000 | 6 |  80 | 
| 1000 |  20 |  1 |  7500 | 8 | 100 |
| 1000 | 100 |  2 | 25000 | 8 |  90 |  
| 2000 | 200 | 10 | 25000 | 6 |  80 |
+------+-----+----+-------+---+-----+   

Answer 1

Another approach:

SELECT
  T2.a
  , T2.b
  , T2.c
  , T2.d AS d
  , MAX(Table3.e) AS e
  , MAX(TABLE3.f) AS f
FROM Table3
JOIN (
  SELECT
    T1.a
    , T1.b
    , T1.c
    , SUM(Table2.d) AS d
  FROM Table2
  JOIN (
    SELECT
      a
      , b
      , MAX(c) AS c
    FROM Table1
    GROUP BY a, b
    ) T1
    ON T1.a = Table2.a
    AND T1.b = Table2.b
    AND T1.c = Table2.c
  GROUP BY T1.a, T1.b, T1.c
) T2
  ON T2.a = Table3.a
  AND T2.b = Table3.b
  AND T2.c = Table3.c
GROUP BY T2.a, T2.b, T2.c
ORDER BY T2.a, T2.b, T2.c
;

See it in action: SQL Fiddle.

Please comment, if and as this requires adjustment / further detail.

Answer 2

SELECT  A.A
      ,B.B
      ,A.C
      ,A.D
      ,B.E
      ,B.F
 FROM (SELECT    T2.A A
                    ,T2.B B
                    ,MAX( T2.C) C
                    ,SUM( T2.D) D
              FROM TABLE2 T2
             WHERE T2.C = (SELECT MAX( T2T.C)
                                  FROM TABLE2 T2T
                                 WHERE T2.A = T2T.A AND T2.B = T2T.B)
         GROUP BY T2.A, T2.B) A
      ,(SELECT   T3.A A
                    ,T3.B B
                    ,MAX( T3.E) E
                    ,MAX( T3.F) F
              FROM TABLE3 T3
             WHERE T3.C = (SELECT MAX( T3T.C)
                                  FROM TABLE3 T3T
                                 WHERE T3.A = T3T.A AND T3.B = T3T.B)
         GROUP BY T3.A, T3.B) B
WHERE A.A = B.A AND A.B = B.B

Answer 3

You could look for the maximums in subqueries. That allows you to use the result to search in the next table. For example:

select  t1.a
,       t1.b
,       t1.max_c
,       t2.sum_d
,       t3.max_e
,       t3.max_f
from    (
        select  a
        ,       b
        ,       max(c) as max_c
        from    table1
        group by
                a
        ,       b
        ) t1
left join    
        (
        select  a
        ,       b
        ,       c
        ,       sum(d) as sum_d
        from    table2
        group by
                a
        ,       b
        ,       c
        ) t2
on      t1.a = t2.a
        and t1.b = t2.b
        and t1.max_c = t2.c
left join    
        (
        select  b
        ,       max(e) as max_e
        ,       max(f) as max_f
        from    table3
        group by
                b
        ) t3
on      t1.b = t3.b

Example at SQL Fiddle.