agora inbox for pgsql-general@postgresql.org
help / color / mirror / Atom feedFrom: Mike Mascari <mascarm@mascari.com>
To: 'Alex Hochberger' <alex@feratech.com>
To: 'Ryan Mahoney' <ryan@paymentalliance.net>
To: 'pgsql-general@postgresql.org' <pgsql-general@postgresql.org>
Subject: RE: SQL help...
Date: Wed, 16 May 2001 01:34:48 -0400
Message-ID: <01C0DDA8.65DB77E0.mascarm@mascari.com> (raw)
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
view thread (15+ messages) latest in thread
Message-ID: <01C0DDA8.65DB77E0.mascarm@mascari.com>
Permalink: ../01C0DDA8.65DB77E0.mascarm@mascari.com/
Also on: postgresql.org/message-id/01C0DDA8.65DB77E0.mascarm@mascari.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: mascarm@mascari.com, alex@feratech.com, ryan@paymentalliance.net
Subject: RE: SQL help...
In-Reply-To: <01C0DDA8.65DB77E0.mascarm@mascari.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox