Michael,
How 'bout doing a subquery that grabs only questions with answers, then having the
main query filter questionID using the IN keyword?
Something like:
--------------------------------
SELECT
HNTQuestion.QuestionID,
HNTQuestion.Category,
HNTQuestion.QuestionShort,
HNTCategories.HNTCategoryID,
HNTCategories.HNTCategoryName
FROM HNTQuestion, HNTCategories
WHERE HNTQuestion.Category = HNTCategories.HNTCategoryID
AND HNTQuestion.QuestionID IN
(select a.QuestionID
from HNTQuestion a, tblAnswers b
where a.QuestionID = b.ANSQuestionID)
ORDER BY HNTQuestion.Category, HNTQuestion.QuestionShort
--------------------------------------------------------
I'm sure I got some of it wrong, but I think that's the direction you'll want to go.
GL,
Alec
> -----Original Message-----
> From: Michael Kear [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, March 28, 2001 5: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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
