Hi,
I'm trying to add Drill down support to a Survey App i've written and
I am having trouble nailing down the SQL.
What I am trying to do is get the responses where a user answer w to
question x and y to question z:
Currently I have the following SQL which works great with 1 question 1 answer:
SELECT Survey_Response_ID
FROM Survey_ClientAnswer
WHERE
(
survey_question_id = 11
AND
Survey_ClientAnswer_Answer = 'Sadie Frost'
)
Now my initial thoughts were to do the following for the drill down:
SELECT Survey_Response_ID
FROM Survey_ClientAnswer
WHERE
(
survey_question_id = 11
AND
Survey_ClientAnswer_Answer = 'Sadie Frost'
)
AND
(
survey_question_id = 12
AND
Survey_ClientAnswer_Answer = 'Star Wars'
)
But this yeilds 0 results, I've basically go it down to Subqueries
filtering Subqueries i.e.
SELECT Survey_Response_ID
FROM Survey_ClientAnswer
WHERE
(
survey_question_id = 12
AND
Survey_ClientAnswer_Answer = 'Star Wars'
)
AND Survey_Response_ID IN (SELECT Survey_Response_ID
FROM
Survey_ClientAnswer
WHERE
survey_question_id = 11
AND
Survey_ClientAnswer_Answer = 'Sadie Frost'
)
But this looks like it could lead to headaches the more questions
filtered on...
Has anyone got any suggestions on a better way to acheive the same results?
Thanks in advance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Purchase Flash MX Pro from House of Fusion, a Macromedia Authorized Affiliate
and support the CF community.
http://www.houseoffusion.com/banners/view.cfm?bannerid=57
Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2222
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
