try this
<cfquery name="getquestions" datasource="#dsn#">
SELECT
HNTQuestion.QuestionID,
HNTQuestion.Category,
HNTQuestion.QuestionShort,
HNTCategories.HNTCategoryID,
HNTCategories.HNTCategoryName
FROM HNTQuestion, HNTCategories, tblAnswers
WHERE HNTQuestion.Category = HNTCategories.HNTCategoryID
AND HNTQuestion.QuestionID = tblAnswers.ANSQuestionID
ORDER BY HNTQuestion.Category, HNTQuestion.QuestionShort
</cfquery>
An answer is linked to its associated question with a field ANSQuestionID
in the table tblAnswers which has the same value as the field
HNTQuestion.QuestionID.
-----Original Message-----
From: Michael Kear [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 28, 2001 4:33 PM
To: CF-Talk
Subject: OT: SQL Question (Access)
I'm sorry if this is off-topic, but I'm hoping for some help from people
who know more about SQL than I do ...
I have a hints'n'tips section on one of my sites, and it has 3 tables -
tblCategories, tblQuestions and tblAnswers. I want to have an index page
that lists the questions in their categories, so you click on the question
and a window opens with all the answers to that question.
Here's the bit I am having trouble with .. A few questions don't have any
answers yet - for example some haven't been loaded up yet. How do I get
SQL to retrieve only the questions that have answers in the Answers table,
leaving out any questions that have no answers?
Here's the SQL I'm using now, that returns all the questions (if it's
relevant here, the database is Access2000):
<cfquery name="getquestions" datasource="#dsn#">
SELECT
HNTQuestion.QuestionID,
HNTQuestion.Category,
HNTQuestion.QuestionShort,
HNTCategories.HNTCategoryID,
HNTCategories.HNTCategoryName
FROM HNTQuestion, HNTCategories
WHERE HNTQuestion.Category = HNTCategories.HNTCategoryID
ORDER BY HNTQuestion.Category, HNTQuestion.QuestionShort
</cfquery>
An answer is linked to its associated question with a field ANSQuestionID
in the table tblAnswers which has the same value as the field
HNTQuestion.QuestionID.
Can anyone help me please?
Cheers,
Mike Kear
Windsor, NSW, Australia
AFP Webworks.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
