SQL join query using rows which only have date of today to sort

I'm sure this is really simple... just tired now.

I have a query

SELECT post.*, votes.datetime, COUNT(votes.post_id) AS votes        
FROM posts LEFT JOIN votes          
ON posts.id = votes.post_id         
GROUP BY posts.id       
ORDER BY votes DESC

I need to fetch all posts on the left table whilst when counting votes on the votes table using the votes which happened today or between 1 hour and another hour of the same day. Does this make enough sense?

I have tried adding

WHERE DATE(votes.datetime) = DATE(NOW())

But no luck, it just shows the rows wh开发者_StackOverflowich have votes which happened today and no other posts rows which have either no votes at all or votes that happened today.

I am using data type datetime for the datetime column

Any ideas how I can do this easily?

Thanks in advance!

-Stefan

Use a subquery in your LEFT JOIN votes expression, specifying the condition you seek as a WHERE clause within this. Then later, if necessary, optimise for performance by transforming away the subquery.

I'm not exactly sure if I understand your problem correctly, but the way I understand it I would suggest usinf LEFT OUTER JOIN instead of LEFT JOIN.

This way you get all the posts, even those without votes.

SELECT post.*, votes.datetime, COUNT(votes.post_id) AS votes        
FROM posts LEFT OUTER JOIN votes          
ON posts.id = votes.post_id         
WHERE DATE(votes.datetime) = DATE(NOW())
GROUP BY posts.id       
ORDER BY votes DESC

LEFT JOIN (SELECT * FROM votes WHERE DATE(datetime) = DATE(NOW())) votes

I suspect this won't give you what you want, since SELECT ... votes.datetime ... will result in each post being listed multiple times, and COUNT(votes.post_id) almost always evaluating to 1.

Unfortunately, I can't grok exactly what you need from your question. (What does "... between 1 hour and another hour of the same day" mean? Also, you can't order by votes.) If you just want a count of today's votes for all posts, you can do this:

SELECT posts.id,
       (SELECT COUNT(*)
          FROM votes
         WHERE DATE(datetime) = DATE(NOW())
           AND post_id = posts.id
       ) AS num_votes
FROM posts