从一个MySQL表中获取条目，可选地在其他表中计算相应的ID

Question

I've tried to find a solution to this for a long time, read a lot of topics about PIVOT, JOIN, etc., but still can't seem to find a solution, so I really need your help.

I'm programming a thumbs up/down system for comments. So, I want to list all my comments AND their current vote sum, plus show whether I (as of userID in comments_votes table) have already voted or not, and all of that, if possible (which I assume) within one SQL query.

I have two MySQL tables:

1) comments

• commentID
• comment
• userID
• topicID

2) comments_votes

• commentID
• userID
• vote (= 1 or -1, for thumb up or down).

<<<<< PART 1: Total Vote Sum >>>>>

In the first part, I want to show the current sum of all votes which would be the SUM(vote) for that specific commentID. The vote sum could should work for every comment, even if a comment hasn't received any votes yet (so if there's no entry in second table for that commentID). So if there's zero entries for that comment in the comments_vote table, then the vote sum is 0.

One of the major problems I encountered here was that I do NOT only want to get the comments for which there is also a vote! This is for example the reason why a simple "," between the two tables and a.commentID=b.commentID didn't work (because that one only returned the ones where theres matching entries for both tables). For the pivot table I never really got it working. The JOIN function also doesn't seem to do the job. Any idea how I could fix this?

<<<<< PART 2: Have I voted already? >>>>>

So for the second part, I want to determine whether my userID is already in the comments_votes table for each comment. In a way that if I have already voted for that comment, I won't be enabled via PHP/HTML to vote again.

-

Right now, I got it working by using one Mysql while(...) and then two functions within this loop, which is of course a terrible solution as it kills site speed. My goal is to get all of that in one SQL query, or some other alternative if that's faster. I need the fastest-running solution.

Your help is GREATLY appreciated! Thanks a lot!!

Answer 1

I think this should work:

select
  c.commentID
, sum(case when cvuser.vote is null then ifnull(cv.vote,0) else 0 end) commentScore
, sum(case when cvuser.vote is null then abs(ifnull(cv.vote,0)) else 0 end) totalCommentScore
, sum(ifnull(cvuser.vote,0))!=0 userHasVoted -- 1 if user has voted, 0 if has not voted
from
  comments c
  left outer join comments_votes cv using (commentID)
  left outer join comments_votes cvuser on c.commentID=cvuser.commentID and cv.userID=<INSERT USER ID HERE>
group by c.commentID