CODEWITHSUNDEEP
mysqlsql
Stefan P
Stefan P

Reputation: 1043

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 which 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

Upvotes: 1

Views: 239

Answers (3)

Marcelo Cantos
Marcelo Cantos

Reputation: 185988

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

Upvotes: 1

Karsten
Karsten

Reputation: 1886

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

Upvotes: 1

evandrix
evandrix

Reputation: 6210

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.

Upvotes: 2

Related Questions