Select From One Table And Include Sum Value From Another

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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)
;

Answer 4

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