Switch your query around and join the answers to the questions, instead of
the questions to the answers.
Hopefully that makes sense.
On Mon, Sep 26, 2011 at 11:43 AM, Adam Bourg adam.bo...@gmail.com wrote:
've built an extension to a employment application where we can easily add
new questions to the form. I need to query to match on both which job they
applied to and what application ID it is. I need to return both the answer
and the question, the problem is it will return both if both are defined,
but I need it to return the question, even if the application isn't defined,
but right now it'll only return the question if the answer is defined.
Please help!
Code: (Note the where uses a Coldfusion variable, so nothing out of the
normal)
SELECT
dbo.mod_employmentAppQuestionAnswers.questionID
,dbo.mod_employmentAppQuestionAnswers.questionDefinitionID
,dbo.mod_employmentAppQuestionAnswers.AppID
,dbo.mod_employmentAppQuestionAnswers.questionText
,dbo.mod_employmentAppQuestionAnswers.questionDate1
,dbo.mod_employmentAppQuestionAnswers.questionDate2
,dbo.mod_employmentAppQuestionAnswers.questionBit
,dbo.mod_employmentAppQuestionDefinitions.definitionID
,dbo.mod_employmentAppQuestionDefinitions.jobTitleID
,dbo.mod_employmentAppQuestionDefinitions.title AS QuestionTitle
,dbo.mod_employmentAppQuestionDefinitions.questionTypeID
,dbo.mod_employmentAppQuestionDefinitions.description
,dbo.mod_employmentAppQuestionDefinitions.isActive
,dbo.mod_employmentAppJobTitles.title AS JobTitle
,dbo.mod_employmentAppQuestionTypes.type AS QuestionType
FROM dbo.mod_employmentAppQuestionAnswers
FULL JOIN dbo.mod_employmentAppQuestionDefinitions
ON dbo.mod_employmentAppQuestionAnswers.questionDefinitionID =
dbo.mod_employmentAppQuestionDefinitions.definitionID
INNER JOIN dbo.mod_employmentAppJobTitles
ON dbo.mod_employmentAppQuestionDefinitions.jobTitleID =
dbo.mod_employmentAppJobTitles.jobTitleID
LEFT JOIN dbo.mod_employmentAppQuestionTypes
ON dbo.mod_employmentAppQuestionDefinitions.questionTypeID =
dbo.mod_employmentAppQuestionTypes.questionTypeID
WHERE
(dbo.mod_employmentAppQuestionDefinitions.jobTitleID =
cfqueryparam cfsqltype=cf_sql_integer
value=#jobTitleID# /) AND
(dbo.mod_employmentAppQuestionAnswers.AppID =
cfqueryparam cfsqltype=cf_sql_integer
value=#applicationID# /)
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:347745
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
