MySQL query (joining tables)

QuestionsTable

id* (int) | question_text (string) | question_type (int)

AlternativesTable

id* (int) | question_id (int) | alternative_text (string) | is_correct (bool)

AnswersTable

id* (int) | question_id (int) | alternative_id (int) | answer_text (string)

" * " = primary key

Every question can either be of type free text or multiple selector. A multiple selector question has one or more alternatives and only one can be correct.

An answer is defined by a question_id and an alternative_id (multiple selector) or an answer_text (free text). The is_correct bool is so I can mark开发者_Go百科 which answer is the correct one.

How do I make an SQL query that will give me all the alternatives listed for every question with a count on each alternative that shows how many has selected it? Say I can store it in an array and iterate through it with an foreach-loop and show it as the example below.

An example, every question is represented by it's question_text, and every alternative beloning to that question is represented by Alt1 (alternative_text), Alt2, and so on... The numbers after the alternatives is the number of selections it got (answers).

MultiQuestion1

Alt1 | 3

Alt2 | 2

Alt3 | 0

MultiQuestion2

Alt1 | 2

Alt2 | 3

FreeText1

Answer1

Answer2

....

I can make the query that gives me all questions and all the alternatives that belong to it, but I fail when I try to get the count on all answers for every question alternative.

So now I could use some help from a SQL-ninja =)

Thanks in advance Daniel

Off the top of my head, this might work:

SELECT alternatives.id, COUNT(DISTINCT(answers.id))
FROM alternatives
LEFT JOIN answers ON alternatives.id = answers.altid
GROUP BY alternatives.id;

That should give you the total number of times each alternative occurs in the answers table.