complex and or query

Hi,
Anyone able to help? XTab wont work.
Have a table containing names of people.
Mark
John
Mary
They answer up to 10 questions each. The answers are
unique id numbers kept in the same column
ResultsTable
QuestionID AnswerID Answer Name
1 001 Apple John
2 005 Holden John
3 010 20 John
1 002 Orange Mary
2 006 VW Mary
3 011 30 Mary
Need to be able to have a stored procedure that allows me
to select (for example) everyone who chose fruit = apple
or orange and cartype = Holden and age > 25
The problem is, doing a simple and returns no results
because of the single column for the AnswerID. Doing an
or will return anyone answering either of these answers.
Any ideas?I'm not sure why you have both answerid and answer columns in this table.
Since answerid appears to be redundant I've ignored it.
CREATE TABLE Answers (questionid INTEGER NOT NULL, answer VARCHAR(10) NOT
NULL, firstname VARCHAR(10) NOT NULL, PRIMARY KEY (firstname,questionid))
INSERT INTO Answers VALUES (1, 'Apple', 'John')
INSERT INTO Answers VALUES (2, 'Holden', 'John')
INSERT INTO Answers VALUES (3, '20', 'John')
INSERT INTO Answers VALUES (1, 'Orange', 'Mary')
INSERT INTO Answers VALUES (2, 'VW', 'Mary')
INSERT INTO Answers VALUES (3, '30', 'Mary')
CREATE TABLE Criteria (questionid INTEGER NOT NULL, min_answer VARCHAR(10)
NOT NULL, max_answer VARCHAR(10) NULL, PRIMARY KEY (questionid, min_answer))
INSERT INTO Criteria VALUES (1,'Apple',NULL)
INSERT INTO Criteria VALUES (1,'Orange',NULL)
INSERT INTO Criteria VALUES (2,'Holden',NULL)
INSERT INTO Criteria VALUES (3,'20','999')
Here's the query that gives people who match at least one answer in the
Criteria table for each of the questions in the Criteria table.
SELECT firstname
FROM Answers AS A
JOIN Criteria AS C
ON A.questionid = C.questionid
AND A.answer BETWEEN C.min_answer AND COALESCE(C.max_answer,
C.min_answer)
GROUP BY firstname
HAVING COUNT(DISTINCT A.questionid)= (SELECT COUNT(DISTINCT questionid)
FROM Criteria)
--
David Portas
--
Please reply only to the newsgroup
--