please help me in this complex mysql query

Question

i have answers table ( ans_id , question_id , ans_date)

---

and answers_votes table (vote_id , user_id , ans_id , vote = 1 or -1)

---

i want select from answers that have the big sum(vote) of vote that posted today this mean have today date in ans_date

Answer 1

Something like this :

select answers.ans_id, count(*) as nb_votes
from answers
    inner join answers_votes on answers_votes.ans_id = answers.ans_id
where answers.ans_date = curdate()
group by answers.ans_id
order by count(*) desc
limit 10

should give you the 10 answers that have the most votes, and have been created today.

If you want more, or less, than 10, you can change the value in the limit clause.

Not tested, as I don't have your database -- so might contain a few mistakes...

Answer 2

select a.ans_id
     , a.question_id
     , a.ans_date
     , (select sum(vote) from answers_votes where ans_id = a.ans_id) as points
     , (select count(*) from answers_votes where ans_id = a.ans_id and vote = 1) as up_votes
     , (select count(*) from answers_votes where ans_id = a.ans_id and vote = -1) as down_votes
  from answers a
 where date(a.ans_date) = date(now())
 order by points desc

Although this now makes the points value somewhat redundant. It could be calculated just the same as doing up_votes - down_votes - provided a vote is always worth only 1 point up or down.