So what I'm actually trying to achieve is filter the most voted on stories. The stories are saved in one table, and all of the votes in another one.

I have this statement to get the vid column order proper:

SELECT vid, COUNT(id) AS votecnt 
  FROM votes 
WHERE status = 1 

And then I have to filter the stories themselves, and I want the to be ordered just as they are in the first clause.

SELECT vid, body, timestamp 
  FROM stories 
WHERE lv = 1 
  AND status = 1

The thing is, it's the the order that I need, because the first statement does not return all of the rows that the second one does. It would be even better, if the first query would sort the table by status = 1 count at first, and then total votes.

Any ideas how to join all this into an SQL query? Or should I use PHP in between?


Unless I'm grossly misunderstanding your structure, a simple join should work fine:

SELECT stories.vid, body, timestamp, COUNT(votes.id) votecnt FROM stories
INNER JOIN votes ON stories.vid=votes.vid
WHERE stories.lv=1 AND stories.status=1
GROUP BY votes.vid

I'm not sure what you mean by

It would be even better, if the first query would sort the table by status = 1 count at first, and then total votes.

since your where statement only uses votes/stories that have status=1.

  • This works great, for the given parameters. The problem is that there's two statuses for votes - 0 and 1. What I would like is for the given results to be sorted first by maximum votes of status 1, and if the maximum votes of status 1 equals to another records' max votes, then it would be sorted by the total number of votes - of status 0 and 1, that is. Another thing - this query doesn't return the records from the stories table if there is no record for it in the votes table. I don't want that.
    – donk
    Apr 12 '11 at 14:25
  • I received all of the needed stories by switching to LEFT JOIN and grouping by stories.vid instead of votes.vid. Now the hardest bit - how do I sort the records properly?
    – donk
    Apr 12 '11 at 14:40
  • Glad you figured out the LEFT JOIN bit. As far as your sorting..assuming the stories.status and votes.status are the same (duplicate content for whatever reason), the query is excluding any rows that do not have a status=1, so rows with status=0 won't be counted. Not really an answer, I'm afraid. Apr 12 '11 at 16:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.