RE: SQL help... - Mailing list pgsql-general

I don't know of a single query that will satisfy your needs (not saying
that there isn't one...) - you might try in whatever language you are using:

select users
for each user
         select questions
         for each question
                 select answers
         end loop
end loop

This is a bit of computation, but if it's just to generate a report you
should be fine.

Good Luck!

-r

At 12:28 AM 5/16/01 -0400, Alex Hochberger wrote:

>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
>
> > -----Original Message-----
> > From: Ryan Mahoney [mailto:ryan@paymentalliance.net]
> > Sent: Tuesday, May 15, 2001 7:22 PM
> > To: Alex Hochberger; 'pgsql-general@postgresql.org'
> > Subject: Re: [GENERAL] SQL help...
> >
> >
> > Please post the sql statement that creates these tables.
> >
> > -r
> >
> > At 12:15 AM 5/16/01 -0400, Alex Hochberger wrote:
> >
> > >To any SQL wizards out there,
> > >
> > >I have finally exhausted my SQL knowledge.
> > >
> > >I have 3 tables that I need to do a fancy join on...
> > >
> > >1 stores the users
> > >1 stores the questions
> > >1 stores the user's answers to the questions (based on
> > foreign keys to the
> > >answers table)
> > >
> > >I would like to create a result with the following columns:
> > >some fields from the users, each of the questions
> > >
> > >in each row should be the results from the users, and their
> > user answers
> > >
> > >Here is the tricky thing, people may have not answered each
> > question, so I
> > >would like to either leave that blank or put in a 0...
> > >
> > >With an ugly hack, I get the results where they answered
> > everything, but not
> > >the partial answers.
> > >
> > >Please cc: me on the reply, because I get this as a digest.
> > >
> > >Thanks,
> > >Alex
> > >
> > >---------------------------(end of
> > broadcast)---------------------------
> > >TIP 2: you can get off all lists at once with the unregister command
> > >     (send "unregister YourEmailAddressHere" to
> > majordomo@postgresql.org)
> > >
> > >
> > >
> > >---
> > >Incoming mail is certified Virus Free.
> > >Checked by AVG anti-virus system (http://www.grisoft.com).
> > >Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01
> >
>
>
>
>---
>Incoming mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01