SQL help...

-----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

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

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)

-----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