From: Per-Olof Pettersson <pgsql@peope.net>
Subject: RE: SQL help...
Date: Wed, 16 May 2001 05:14:52 GMT
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 156
Message-ID: <20010516.5145278@mis.configured.host>
References: <1F3774AB3688D4118B1300508BD9641528A7E1@CHINA>
	<20010516.5102196@mis.configured.host>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
In-Reply-To: <20010516.5102196@mis.configured.host>
To: pgsql-general@postgresql.org
Content-Transfer-Encoding: 8bit

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)