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 <[email protected]> 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
