Обсуждение: SQL help...
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
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 --- 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
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
>
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
Hi
I think this is a matter of an outer join.
SELECT *
FROM users, questions LEFT JOIN answers ON questions.question_id =
answers.question_id;
Note that the outer join is implemented in 7.1.x.
Best regards
Per-Olof Pettersson
>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
On 2001-05-16, 06:41:34, alex@feratech.com (Alex Hochberger) wrote
regarding RE: SQL help...:
> 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
> >
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Hi
Sorry it should be
SELECT *
FROM users, questions LEFT JOIN answers ON questions.question_id =
answers.question_id AND users.user_id = answers.user_id
Otherwise you'd get a h*ll lot more rows than expected ;-)
Regards
Per-Olof Pettersson
>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
On 2001-05-16, 07:10:21, Per-Olof Pettersson <pgsql@peope.net> wrote
regarding RE: SQL help...:
> Hi
> I think this is a matter of an outer join.
> SELECT *
> FROM users, questions LEFT JOIN answers ON questions.question_id =
> answers.question_id;
> Note that the outer join is implemented in 7.1.x.
> Best regards
> Per-Olof Pettersson
> >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
> On 2001-05-16, 06:41:34, alex@feratech.com (Alex Hochberger) wrote
> regarding RE: SQL help...:
> > 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
> > >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
The outer join approaches didn't appear to work, and I'm no longer convinced
that this is doable... I brute forced it... I'll worry about it later...
Alex
> -----Original Message-----
> From: Per-Olof Pettersson [mailto:pgsql@peope.net]
> Sent: Wednesday, May 16, 2001 1:10 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] RE: SQL help...
>
>
> Hi
>
> I think this is a matter of an outer join.
>
> SELECT *
> FROM users, questions LEFT JOIN answers ON questions.question_id =
> answers.question_id;
>
> Note that the outer join is implemented in 7.1.x.
>
> Best regards
> Per-Olof Pettersson
>
> >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
>
> On 2001-05-16, 06:41:34, alex@feratech.com (Alex Hochberger) wrote
> regarding RE: SQL help...:
>
>
> > 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
> > >
>
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
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
In article <1F3774AB3688D4118B1300508BD9641528A7E0@CHINA>, Alex Hochberger <alex@feratech.com> writes: > 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... Sounds like a LEFT OUTER JOIN.