JOIN over multiple columns?

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 expre开发者_高级运维ss this in SQL?

Thanks in advance, Frank

---

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.

---

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

---

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.

---

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

---

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.

---

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

---

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.