1

Three tables: questions, answers, and votes.

Fields in questions: qid, uid
Fields in answers: qid, aid, uid
Fields in votes: qid, aid, uid

(uid=userid, will be different for questions, answers, votes)

So 2 votes for answer 100 to question 4 would look like:

questions:

qid=4, uid=1

answers:

aid=100, qid=4, uid=2

votes:

aid=100, qid=4, uid=5
aid=100, qid=4, uid=6

Additionally, the answers table has a time field, adate.

How can I create a query that will show: All fields from questions and the single related row from answers with the most related records in votes, or if there is a tie number of votes, the answer with the earliest date (in other words, the answer with the most votes or, if no votes or a tie, the answer that was submitted first)?

I want to show all questions regardless of if there are answers and all answers regardless of if there are votes.

Seems like 2 left joins, some kind of count(), a select max, and an order_by of some kind would work, but haven't been able to get this to work - for example I started with something like this, which gets to the correct counts for number of votes:

SELECT
votes.aid,
Count(*) AS total,
questions.qid
FROM
votes
INNER JOIN (SELECT votes.aid FROM votes GROUP BY votes.aid) AS answers ON answers.aid= votes.aid
LEFT OUTER JOIN questions ON questions.qid = votes.qid
GROUP BY votes.aid
key2starz
  • 747
  • 4
  • 11
  • 23
  • The uid in answers is not related to the uid in votes, is it? You have users answering a question, and users voting on that answer? – David Faber Mar 10 '12 at 04:11
  • @OMG I only got as far as: SELECT votes.aid, Count(*) AS total, questions.qid FROM votes INNER JOIN (SELECT votes.aid FROM votes GROUP BY votes.aid) AS answers ON answers.aid= votes.aid LEFT OUTER JOIN questions ON questions.qid = votes.qid GROUP BY votes.aid – key2starz Mar 10 '12 at 04:42
  • @key2starz: Update your question to include the query. – OMG Ponies Mar 10 '12 at 04:44
  • @DavidFaber No, unrelated. I edited for clarity. Users can't vote for their own answers, and they can't answer their own questions. – key2starz Mar 10 '12 at 04:45
  • MySQL doesn't have window functions, does it? In can see how to do it in one query with those functions, but not readily without. – David Faber Mar 10 '12 at 12:18
  • Maybe this is just one to do with 2 queries? ;-) – key2starz Mar 13 '12 at 14:20

0 Answers0