Message-ID: <01C0DDA8.65DB77E0.mascarm@mascari.com>
From: Mike Mascari <mascarm@mascari.com>
Reply-To: "mascarm@mascari.com" <mascarm@mascari.com>
To: "'Alex Hochberger'" <alex@feratech.com>, "'Ryan Mahoney'"
	<ryan@paymentalliance.net>, "'pgsql-general@postgresql.org'"
	<pgsql-general@postgresql.org>
Subject: RE: SQL help...
Date: Wed, 16 May 2001 01:34:48 -0400
Organization: Mascari Development Inc.
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

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