Well DOH!! Thanks to all of you who pointed out to me the blindingly
obvious - all I had to do was join the tables. Jeez, I knew that .. I had
just momentarily forgotten is all. Bob's answer was one of many who pointed
out what I should obviously have known and recalled.
The only extra piece that was needed was to allow for the fact that there
were several answers for some questions, so I had to add Distinct to the SQL
like so:
SELECT DISTINCT
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
Thanks a lot for all your patience and help folks.
Cheers,
Mike Kear
Windsor, NSW, Australia
AFP WebWorks
-Original Message-
From: Bob Silverberg [[EMAIL PROTECTED]]
Just add the tblAnswers table to your join, like so:
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
Bob
-Original Message-
From: Michael Kear [mailto:[EMAIL PROTECTED]]
Sent: 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):
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
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
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists