Use order from another select clause

Question

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 
GROUP BY vid 
ORDER BY votecnt DESC

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?

Answer 1

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
ORDER BY votecnt DESC

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.