How about:
SELECT users.user_id, questions.question, user_answers.qa_id
FROM users, questions, user_answers
WHERE users.user_id = user_answers.user_id AND
questions.question_id = user_answers.question_id
UNION
SELECT users.user_id, questions.question, '<No Answer>'
FROM users, questions
WHERE NOT EXISTS (
SELECT 1 FROM user_answers
WHERE user_answers.user_id = users.user_id AND
user_answers.question_id = questions.question_id);
You'll get the user, the question, and his answer if an answer
exists. Otherwise, for each user and for each question posed to that
user, you'll get the user, the question, and <No Anwser>. Is that
what you wanted?
Hope that helps,
Mike Mascari
mascarm@mascari.com
-----Original Message-----
From: Alex Hochberger [SMTP:alex@feratech.com]
Users:
----------------------
CREATE TABLE "users" (
"user_id" int8 DEFAULT nextval('user_id_seq'::text) NOT NULL,
"group_id" int4 NOT NULL,
"user_agent" varchar(200) NOT NULL,
"ip_address" varchar(20) NOT NULL,
CONSTRAINT "users_pkey" PRIMARY KEY ("user_id")
);
CREATE UNIQUE INDEX "user_id_users_ukey" ON "users" ("user_id");
CREATE INDEX "users_group_id_key" ON "users" ("group_id");
CREATE INDEX "users_ip_address_key" ON "users" ("ip_address");
CREATE INDEX "users_user_agent_key" ON "users" ("user_agent");
Questions:
----------------------
CREATE TABLE "questions" (
"question_id" int8 DEFAULT nextval('question_id_seq'::text) NOT
NULL,
"survey_id" int4 NOT NULL,
"question" text NOT NULL,
CONSTRAINT "questions_pkey" PRIMARY KEY ("question_id")
);
CREATE INDEX "questions_question_key" ON "questions" ("question");
CREATE INDEX "questions_survey_id_key" ON "questions" ("survey_id");
User Answers:
----------------------
CREATE TABLE "user_answers" (
"ua_id" int8 DEFAULT nextval('ua_id_seq'::text) NOT NULL,
"user_id" int8 NOT NULL,
"question_id" int8 NOT NULL,
"qa_id" int8 NOT NULL,
CONSTRAINT "user_answers_pkey" PRIMARY KEY ("ua_id")
);
CREATE INDEX "user_answers_qa_id_key" ON "user_answers" ("qa_id");
CREATE INDEX "user_answers_question_id_key" ON "user_answers"
("question_id");
CREATE INDEX "user_answers_user_id_key" ON "user_answers"
("user_id");
All these questions will be for survey 1...
Alex
