Join 3 tables and count number of rows from the last one 1

[Smarty]

I have 3 tables:
1st table: SURVEY (surveyid, surveyquestion,...)
2nd table: OPTION (surveyid, optionid, optionname,...)
3rd table: ANSWER (surveyid, optionid, answerid, answertime,...)

With this 3 tables, I have a 'Question of the week' on my webpage. Filling these tables (ANSWER for the votes) is not a problem, but I want a query for the result with these fields:
Surveyquestion, Optionname, number of answers for this surveyid and optionid.
So that I have in 1 query all my desired fields, and that I can code
Survey 1:
- Option 1 (34 votes)
- Option 2 (59 votes)
...

Can you help me with this query? I would start from the OPTION table, do an inner join on SURVEY, but the count(answerid) is not completly clear to me.

Thanks in advance,

(not so) Smarty

 

[PHV]

SELECT S.surveyquestion, ptionname, COunt(*) AS CountOfVotes
FROM (ANSWER A
INNER JOIN SURVEY S ON A.surveyid = S.surveyid)
INNER JOIN OPTION O ON A.surveyid = O.surveyid AND A.optionid = ptionid
GROUP BY S.surveyquestion, ptionname

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886