从一个表中选择并包含另一个的总和值

My title is terrible but I'm having a hard time wrapping my head around this. I have two tables. Links and Votes. Links contain info about links submitted and the usual jazz, votes contains a foreign key link_id and a karma_up and karma_down value (both unsigned ints). Because I want to limit votes to one per user and record vote time I opted for the two tables method. What I want to do though is display the sum'ed karma of a link. I need to be able to grab all the links and then their one to many votes which are sum'ed up SUM(karma_up - karma_down). All I have been able to do is SUM up the entire column which doesn't do me any good.

Hopefully someone just "gets" what I'm trying to do but till then I will be updating this post with as much relevant information as possible.

UPDATE For anyone interested, my final query is

SELECT links.*, (SUM(karma_up) - SUM(karma_down)) AS karma
FROM links, votes
WHERE links.id = votes.link_id
GROUP BY votes.link_id

You could post your schema to be sure, but something like this should work

select v.link_id, (sum(karma_up) - sum(karma_down)) as points from 
Links l, Votes v
where l.link_id = v.link_id group by v.link_id

That should give you the points per link_id.

You need to do something like this:

SELECT Links.LinkId, SUM(Votes.karma_up) - SUM(Votes.karma_down) AS karma FROM Links
    LEFT JOIN Votes ON Links.LinkId = Votes.LinkId
    GROUP BY Links.LinkId

Note the use of LEFT JOIN to ensure that links without votes are also included.

Points per link id, and zero karma if there are no votes:

select l.*, coalesce(sub_karma, 0) as karma
from links as l
    left join (
        select link_id, sum(karma_up - karma_down) as sub_karma
        from votes
        group by link_id
    ) as v using (link_id)
;

Just one more thing I'd like to mention. Unless a vote is allowed to have both up and down karma at the same time (unlikely from the desription), you would better off replacing them karma_up and karma_down with a single karma_delta column. Then set it to -1 or 1 depending on whether it's down or up respectively.

This would simplify your query even more:

SELECT links.*, SUM(karma_delta) AS karma
FROM links, votes
WHERE links.id = votes.link_id
GROUP BY votes.link_id