JOIN over multiple columns?

Question

Hi folks,

I have a database-table with the following structure:

id1 | id2 | weight

Now I want to sum up the weight for all entries, that have in id1 or id2 a given value.

SELECT id, SUM(weight) FROM myTable WHERE id1=@id or id2=@id

Usually, I need to get the sum for more than one id. To speed things up, I first load the ids for which the sum is needed in a temporary table. Now I do not know how to make the join, so that I get the sum for all ids. If only one column would be included, it would be

SELECT i.Id, SUM(weight)
FROM @IDs i JOIN myTable s1 ON i.Id=s1.id1 
GROUP BY i.Id

but then I would not get the entries where id2=@id. I could use a union in the following way:

SELECT i.Id, SUM(weight)
FROM @IDs i JOIN myTable s1 ON i.Id=s1.id1 
GROUP BY i.Id
UNION
SELECT i.Id, SUM(weight)
FROM @IDs i JOIN myTable s2 ON i.Id=s1.id2 
GROUP BY i.Id

but then I would get 2 sums, one cumulated over id1=@id and a second over id2=@id. But I want one result cumulated over both columns.

Anybody knows how to express this in SQL?

Thanks in advance, Frank

Answer 1

Quick and dirty would be to create a temporary table, insert the records from an id match into it, then insert the records from the id2 match, and then finally select the sum out of that temp table.

Answer 2

select t.id, Sum(s1.weight), Sum(s2.weight)
from table1 t
join table2 s1 on t.id1 = s1.id
join table2 s2 on t.id2 = s2.id
group by t.id

Edit: you'd prbably need the weight columns in the group by too.

Answer 3

why don't you extend your union solution in this way

select id,sum(s)
from(
  SELECT i.Id, SUM(weight) s
   FROM @IDs i JOIN myTable s1 ON i.Id=s1.id1 
   GROUP BY i.Id
   UNION
   SELECT i.Id, SUM(weight)
   FROM @IDs i JOIN myTable s2 ON i.Id=s1.id2 
   GROUP BY i.Id
)
group by id

Answer 4

SELECT a.ID, SUM(a.Weight) FROM
(SELECT i.Id, weight
FROM @IDs i 
JOIN myTable s1 
 ON i.Id=s1.id1 
GROUP BY i.Id
UNION
SELECT i.Id, weight
FROM @IDs i 
JOIN myTable s2 
 ON i.Id=s1.id2 
GROUP BY i.Id) a

IF I understand you corectly this might be what you want.

Answer 5

SELECT  id, SUM(weight)
FROM    (
        SELECT  i.id, s1.weight
        FROM    @IDs i
        JOIN    myTable s1
        ON      s1.id1 = i.id
        UNION ALL
        SELECT  i.id, s1.weight
        FROM    @IDs i
        JOIN    myTable s1
        ON      s1.id2 = i.id
        ) q
GROUP BY
        id

or this:

SELECT  i.id, SUM(CASE WHEN id = id1 THEN weight ELSE 0 END + CASE WHEN id = id2 THEN weight ELSE 0 END)
FROM    @ids i
JOIN    mytable s
ON      i.id IN (s.id1, s.id2)
GROUP BY
        id

The first one is more efficient if few records in mytable satisfy the conditions, the second one is more efficient if many records do.

Answer 6

SELECT
     MT.id1,
     MT.id2,
     SUM(MT.weight)
FROM
     @IDs IDs
INNER JOIN My_Table MT ON
     MT.id1 = IDs.id OR
     MT.id2 = IDs.id
GROUP BY
     id1,
     id2

Or if you want the weights double-counted (once for id1 and once for id2) then:

SELECT
     IDs.id,
     SUM(MT.weight)
FROM
     @IDs IDs
INNER JOIN My_Table MT ON
     MT.id1 = IDs.id OR
     MT.id2 = IDs.id
GROUP BY
     id1,
     id2

Answer 7

I think you just need to add an OR to your JOIN clause:

SELECT i.Id, SUM(weight)
FROM @IDs i JOIN myTable s1 ON i.Id=s1.id1 OR i.Id=s1.id2
GROUP BY i.Id