Dynamically constructing query...advice sought

From: Jeremy <newspostings_at_hazelweb.co.uk>
Date: Fri, 9 Jul 2004 12:01:33 +0100
Message-ID: <MPG.1b588b5e19913890989c90@news.individual.net>

Oracle 8i (8.1.7+) and 9i R2

Take the following simplified table structure

---

PEOPLE

person_id        number
name             varchar2(30)


QUESTIONS
question_id      number
question         varchar2(30)
answer_type      varchar2(30) -- DATE, NUMBER, FREEFORM, Y/N, PICKLIST
picklist_id       number


ANSWERS
answer_id        number
person_id        number       -- FK to PEOPLE
question_id      number       -- FK to QUESTIONS
answer           varchar2(4000)



-------------------------------------------------------

Then we have a bunch of people who have answered a load of questions.

What I am looking into is the feasibility of providing a web interface (easy part!) where a user can specify criteria for listing PEOPLE based on their ANSWERS to QUESTIONS

The user would be presented with a form on which would be listed the questions. Against each one, I anticipate fields to allow entry of value to be matched or min and max (for date or number range) etc. or a pick list of values depending on the ANSWER_TYPE column value.

Then based on the values submitted by the users, construct a query dynamically starting:

SELECT NAME

FROM   PEOPLE     P,
       QUESTIONS  Q,
       ANSWERS    A

WHERE P.PERSON_ID = A.PERSON_ID
AND Q.QUESTION_ID = A.QUESTION_ID
AND (dynamically_generated_criteria)

All criteria would be ANDed.

Would I have to join into the QUESTIONS and ANSWERS table once per QUESTION selected by the user?

Anyone done something similar, have any wisdom to share on this?

Not sure if I have explained as clearly on here as it is in my head!

-- 

jeremy

Received on Fri Jul 09 2004 - 06:01:33 CDT