RE: SQL help...
От | Ryan Mahoney |
---|---|
Тема | RE: SQL help... |
Дата | |
Msg-id | 5.0.2.1.0.20010516003040.0473a930@paymentalliance.net обсуждение исходный текст |
Ответ на | RE: SQL help... (Alex Hochberger <alex@feratech.com>) |
Список | 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
В списке pgsql-general по дате отправления: