MySQL: Group and sum from different tables. Help!

Question

Let's suppose we have three tables

T1

 ID |Type| Class | Points
 111|1   |a101   | 12
 111|1   |b104   | 10
 112|2   |a112   | 40
 118|1   |a245   | 30
 186|2   |c582   | 23

T2(Data for Type = 1 only)

 ID |Type|EPoints
 111|1   |4
 118|1   |3

T3(Data for Type = 2 only)

 ID |Type|EPoints
 112|2   |9
 186|2   |15

And we want to have a View which will show ID,Type,sum(Points)+Epoints for example

 ID |Type| Points
 111|1   | 26
 112|2   | 49
 118|1   | 33
 186|2   | 38

How can I do that?

Answer 1

select dt.id
     , dt.type
     , dt.points + coalesce(t2.epoints, t3.epoints) as points
  from (select t1.id
             , t1.type
             , sum(t1.points) as points
          from t1
        group
            by t1.id) as dt
left outer
  join t2
    on t2.type = dt.type
left outer
  join t3
    on t3.type = dt.type

Answer 2

Looks like this might work (the join may need to be a LEFT one -- with a COALESCE around Epoints to transform nulls into zeros -- if you need to take precautions about records in t1 without any matching record in either of the other tables, of course).

SELECT t1.ID, t1.Type, SUM(t1.Points) + other.Epoints
FROM t1
JOIN (
  t2
  UNION
  t3
) other ON (other.Type = t1.Type)
GROUP BY t1.ID