'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:347707
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
