How can I join these two tables?

Question

I have one table (t1)

id    score
22     1
35     3
51     4

and another (t2)

id    score
22     2
35     1
10     5

I want to create the following table using an SQL query.

id    score
10      5
22      3
35      4
51      5

i.e. I need to merge the id and add the scores together.

Ideally in ANSI SQL.

@Prix, a) please post answers in the answer section, b) doing inserts when you can do selects is BAD advice — Unreason, Nov 10 '10 at 14:02
@Prix. Roger already HAS the data, he wants to SELECT it. he does not need to create a table; select from current; then select from it !?!?! — PerformanceDBA, Nov 10 '10 at 14:33

score 2 · Answer 1 · answered Nov 10 '10 at 14:00

2

SELECT  id, SUM(score)
FROM    (
        SELECT  *
        FROM    t1
        UNION ALL
        SELECT  *
        FROM    t2
        ) q
GROUP BY
        id

answered Nov 10 '10 at 14:00

Quassnoi

413,100
91
616
614

2

@Unreason: it's quite obvious from the example they are not, isn't it? – Quassnoi Nov 10 '10 at 14:04
@Quassnoi, I am rushing a bit today, but no, can't say that I see any duplication of ID per table. – Unreason Nov 10 '10 at 14:18
@Unreason: per table no, there is no duplication. The @op, however, said he wanted to "merge the id and add the scores together". – Quassnoi Nov 10 '10 at 14:23
@Quassnoi: it shows I have adopted your formatting rules (much better btw) but is there a reason I see "q" showing up in a lot of your queries? – Lieven Keersmaekers Nov 10 '10 at 14:23
@Lieven: it's shortcut for "query" and on my keyboard I can find it easily with my left ring finger. BTW, the innermost `SELECT` should line up too! – Quassnoi Nov 10 '10 at 14:25
I've noticed but that clashes with your INNER JOIN (...) style. – Lieven Keersmaekers Nov 10 '10 at 14:45
1

@Lieven: if the keyword is longer than `7` characters, the rest is carried over to the next line. The table name is on the same line with `JOIN`, but on the next line after `LEFT JOIN`. See here: http://stackoverflow.com/questions/806882/update-multiple-tables-in-mysql-using-left-join – Quassnoi Nov 10 '10 at 14:55
Each his own offcourse but that LEFT JOIN formatting is not my cup of tea. I prefer the LEFT JOIN to align with the table it is joining. – Lieven Keersmaekers Nov 10 '10 at 15:06

score 2 · Answer 2 · answered Nov 10 '10 at 14:01

Use a UNION ALL to combine all records of both tables into one and a GROUP BY to get the sum for each ID.

SELECT  id, SUM(score)
FROM    (
          SELECT  id, score
          FROM    t1      
          UNION ALL 
          SELECT  id, score
          FROM    t2
        ) t
GROUP BY
        t.ID

score 0 · Answer 3 · answered Nov 10 '10 at 14:03

create table a(id int, score int)
create table b(id int, score int)

insert into a values(1, 10)
insert into a values(2, 5)
insert into b values(1, 15)
insert into b values(3, 20)

select id, sum(score) from 
(select * from a
 union all
select * from b) s
group by id

How can I join these two tables?

3 Answers3