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