How to add 2 temporary tables together.

Question

If I am creating temporary tables, that have 2 columns. id and score. I want to to add them together.

The way I want to add them is if they each contain the same id then I do not want to duplicate the id but instead add the scores together.

if I have 2 temp tables called t1 and t2

and t1 had:

id 3 score 4
id 6 score 7

and t2 had:

id 3 score 5
id 5 score 2

I would end up with a new temp table containing:

id 3 score 9
id 5 score 2
id 6 score 7

The reason I want to do this is, I am trying to build a product search. I have a few algorithms I want to use, 1 using fulltext another not. And I want to use both algorithms so I want to create a temporary table based on algorithm1 and a temp table based on algorithm2. Then combine them.

Answer 1

Perform a full outer join on the ID. Select on the ID and the sum of the two "score" columns after coalescing the values to 0.

Answer 2

How about:

SELECT id, SUM(score) AS score FROM (
  SELECT id, score FROM t1
  UNION ALL
  SELECT id, score FROM t2
) t3
GROUP BY id

Answer 3

This is untested but you should be able to perform a union on the two tables and then perform a select on the results, grouping the fields and adding the scores

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

Answer 4

select id, sum(score)
from (
 select * from table 1
 union all
 select * from table2
) tables
group by id

Answer 5

SELECT id, SUM(score) FROM
(
    SELECT id, score FROM #t1
    UNION ALL
    SELECT id, score FROM #t2
) AS Temp
GROUP BY id

Answer 6

You need to create an union of those two tables then You can easily group the results.

SELECT id, sum(score) FROM 

(
 SELECT id, score FROM t1
 UNION 
 SELECT id, score FROM t2
) as tmp
GROUP BY id;