How to select a random result for each distinct item in MySQL

Question

I currently have the following tables

• Questions
• Answers
• Users
• Votes

Sample table set would be like so Question Table:

question_id  | question
1            | Question #1
2            | Question #2
3            | Question #3
4            | Question #4
5            | Question #5

Answers Table:

answer_id      | answer      | user_id      | question_id
1              | Answer #1   | 2            | 1
2              | Answer #2   | 2            | 2
3              | Answer #3   | 3            | 2
4              | Answer #4   | 1            | 3
5              | Answer #6   | 1            | 2
6              | Answer #7   | 2            | 5
7              | Answer #8   | 1            | 5
8              | Answer #9   | 3            | 1
9              | Answer #10  | 2            | 5

User Table:

user_id   | name 
1         | user #1
2         | user #2
3         | user #3

Votes Table:

vote_id   | vote_type   | user_id  | answer_id
1         | 1           | 1        | 1
2         | 0           | 3        | 1
3         | 0           | 3        | 8
4         | 1           | 2        | 3
5         | 1           | 2        | 4
6         | 0           | 1        | 4
7         | 1           | 3        | 3
8         | 0           | 1        | 5
9         | 1           | 1        | 2

I need help writing a query that will allow me to display each item once (even those without an answer) with a random answer and the total number of votes it has received.

Example Result:

[Question 1 | Answer_id = 8   | user_id = 3   | total votes: 1  ]
[Question 2 | Answer_id = 3   | user_id = 3   | total votes: 2  ]
[Question 3 | Answer_id = 4   | user_id = 1   | total votes: 2  ]
[Question 4 | Answer_id = n/a | user_id = n/a | total votes: n/a]
[Question 5 | Answer_id = 10  | user_id = 2   | total votes: 0  ]

And if I was to refresh the query it would output the same questions with random answers (if a different answer is available).

Appreciate any help.

Thanks.

Answer 1

You can try this solution:

SELECT a.question, a.answer_id, a.user_id, a.totalvotes
FROM
(
    SELECT a.question, b.answer_id, c.user_id, COUNT(d.vote_id) AS totalvotes
    FROM questions a
    LEFT JOIN answers b ON a.question_id = b.question_id
    LEFT JOIN users c ON b.user_id = c.user_id
    LEFT JOIN votes d ON b.answer_id = d.answer_id
    GROUP BY a.question, b.answer_id
    ORDER BY RAND()
) a
GROUP BY a.question

---

SQLFiddle Demo

^ You can keep hitting the "Run SQL" button and different answers will appear.

Answer 2

You can use ORDER BY RAND() in your query.