MySQL query help (involving joins?)

Question

Although I've figured out several queries that almost do this, I can't quite get it perfectly and I'm getting frustrated. Here is the setup:

Table: Issue
| id | name | value |
+-------------------+
| 1  |  a   |   10  |
| 2  |  b   |   3   |
| 3  |  c   |   4   |
| 4  |  d   |   9   |

Table: Link
| source | dest |
+---------------+
|   1    |   2  |
|   1    |   3  |

The link table sets up a source/dest relationship between rows in the issue table. Yes, I know this is normalized terribly, but I did not create this schema even though I now have to write queries against it :(.

What I want is results that look like this:

| name | value |
+--------------+
|  a   |  17   |
|  d   |  9    |

The values in the results should be the sum of the values in the issue table when you aggregate together a source with all its dests along with the name of the source.

Some notes (1) A source->dest is a one->many relationship. (2) The best answer will not have any hardcoded id's or names in the query (meaning, it will be generalized for all setups like this). (3) This is in MySQL

Thank you and let me know if I should include any more information

Answer 1

Its fairly simple, but the stickler is the fact that A is not a destination of A yet it is included in the table. The robust solution would involve modifying the data to add

Table: Link
| source | dest |
+---------------+
|   1    |   1  |
|   1    |   2  |
|   1    |   3  |

Then a simple

SELECT a.name, SUM(d.value) FROM 
Issues as a
 JOIN Link as b on a.id=b.source
 JOIN Issues AS d on  b.dest=d.id;
 GROUP BY a.name;

If you can't modify the data.

SELECT a.name, SUM(d.value)+a.value FROM 
Issues as a
 JOIN Link as b on a.id=b.source
 JOIN Issues AS d on  b.dest=d.id;
 GROUP BY a.name,a.value;

MAY work.

Answer 2

SELECT S.name, S.value + SUM(D.value) as value
FROM Link AS L
  LEFT JOIN Issue AS S ON L.source = S.id
  LEFT JOIN Issue AS D ON L.dest = D.id
GROUP BY S.name

Answer 3

This one should return the SUM of both source and dests, and only return items which are source.

SELECT s.name, COALESCE( SUM(d.value), 0 ) + s.value value
FROM Issue s
LEFT JOIN Link l ON ( l.source = s.id )
LEFT JOIN Issue d ON ( d.id = l.dest )
WHERE s.id NOT IN ( SELECT dest FROM Link )
GROUP BY s.name, s.value
ORDER BY s.name;

Answer 4

You could use a double join to find all linked rows, and add the sum to the value of the source row itself:

select      src.name, src.value + sum(dest.value)
from        Issue src
left join   Link l
on          l.source = src.id
left  join  Link dest
on          dest.id = l.dest
group by    src.name, src.value