Skip site navigation (1) Skip section navigation (2)

multiple joins

From: daniel holtzman <daddy2times(at)yahoo(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: multiple joins
Date: 2001-12-06 07:00:49
Message-ID: 20011206070049.44427.qmail@web10905.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
[sorry for the length of this post, but I have many
tables and big queries ;-)]

My question is how to successfully incorporate
_another_ table into an already multiple join.  See
below for more details.

Quick background:
This is a large (~1500 questions) research survey
comprised of many sections.  Each section is comprised
of many questions.  Each question can either require a
single, text-like, response or a selection of multiple
choices.  Responses are recorded in the answers table.
 The first query (which already works) gets all
information from the database to display an empty
form.  Now, I need to design a second query that will
do that, _plus_ retrieve any existing answers for a
given respondent.

I currently accomplish this by running two queries,
the first, to fetch the display information and a
second, to fetch any existing answers, and I am
merging them in application code.  This is inelegant,
and I _should_ be able to do this with a single query.

Relevant tables in my schema from pg_dump:
CREATE TABLE "surveys" (
	"surid" integer NOT NULL,
	"survey_complete" boolean DEFAULT 'f',
	"mrn_e" bytea,
	"pid" integer NOT NULL,
	"orgid" integer NOT NULL,
	Constraint "surveys_pkey" Primary Key ("surid")
);

CREATE TABLE "questions_text" (
	"qtid" integer DEFAULT
nextval('"questions_text_qtid_seq"'::text) NOT NULL,
	"qttext" text NOT NULL,
	Constraint "questions_text_pkey" Primary Key ("qtid")
);

CREATE TABLE "sections" (
	"sxid" integer DEFAULT
nextval('"sections_sxid_seq"'::text) NOT NULL,
	"sxname" text NOT NULL,
	"sxord" integer,
	Constraint "sections_pkey" Primary Key ("sxid")
);

CREATE TABLE "choices" (
	"cid" integer DEFAULT
nextval('"choices_cid_seq"'::text) NOT NULL,
	"ctext" text NOT NULL,
	Constraint "choices_pkey" Primary Key ("cid")
);

CREATE TABLE "surveys" (
	"surid" integer NOT NULL,
	"survey_complete" boolean DEFAULT 'f',
	"mrn_e" bytea,
	"pid" integer NOT NULL,
	"orgid" integer NOT NULL,
	Constraint "surveys_pkey" Primary Key ("surid")
);

CREATE TABLE "disp_types" (
	"dtid" integer DEFAULT
nextval('"disp_types_dtid_seq"'::text) NOT NULL,
	"disptyp" character varying(15) NOT NULL,
	Constraint "disp_types_pkey" Primary Key ("dtid")
);

CREATE TABLE "questions" (
	"qid" integer DEFAULT
nextval('"questions_qid_seq"'::text) NOT NULL,
	"qtid" integer NOT NULL,
	"qord" integer NOT NULL,
	"ssxid" integer NOT NULL,
	"dtid" integer NOT NULL,
	"fmid" integer,
	"vrid" integer,
	"qrequired" boolean DEFAULT 'FALSE',
	Constraint "questions_pkey" Primary Key ("qid")
);

CREATE TABLE "questions_choices" (
	"qcid" integer DEFAULT
nextval('"questions_choices_qcid_seq"'::text) NOT
NULL,
	"qcord" integer NOT NULL,
	"qcval" integer NOT NULL,
	"cid" integer NOT NULL,
	"qid" integer,
	Constraint "questions_choices_pkey" Primary Key
("qcid")
);

CREATE TABLE "sub_sections_text" (
	"sstid" integer DEFAULT
nextval('"sub_sections_text_sstid_seq"'::text) NOT
NULL,
	"ssttext" text,
	Constraint "sub_sections_text_pkey" Primary Key
("sstid")
);

CREATE TABLE "sub_sections" (
	"ssxid" integer DEFAULT
nextval('"sub_sections_ssxid_seq"'::text) NOT NULL,
	"sstid" integer NOT NULL,
	"ssxord" integer NOT NULL,
	"ssxused" boolean DEFAULT 't'::bool NOT NULL,
	"sxid" integer NOT NULL,
	Constraint "sub_sections_pkey" Primary Key ("ssxid")
);

CREATE TABLE "validation_rules" (
	"vrid" integer DEFAULT
nextval('"validation_rules_vrid_seq"'::text) NOT NULL,
	"vrule" text NOT NULL,
	Constraint "validation_rules_pkey" Primary Key
("vrid")
);

CREATE TABLE "answers" (
	"aid" integer DEFAULT
nextval('"answers_aid_seq"'::text) NOT NULL,
	"answer" text NOT NULL,
	"qid" integer NOT NULL,
	"surid" integer NOT NULL,
	"uid" character varying(10) NOT NULL,
	"timestamp" timestamp with time zone NOT NULL,
	Constraint "answers_pkey" Primary Key ("aid")
);



Query #1; current query that _does_ work:
SELECT
  sx.sxname,
  sst.ssttext,
  ssx.ssxused,
  q.qid,
  d.disptyp,
  qt.qttext,
  v.vrule,
  qc.qcval,
  c.ctext,
  q.qrequired

FROM
  questions q LEFT OUTER JOIN questions_choices qc ON
q.qid = qc.qid
              LEFT OUTER JOIN choices c ON qc.cid =
c.cid,
  sections sx,
  sub_sections ssx,
  sub_sections_text sst,
  disp_types d,
  questions_text qt,
  validation_rules v

WHERE
  sx.sxid = $sxid AND       // $sxid is a run-time
variable (PHP) 
  ssx.sxid = sx.sxid AND
  ssx.sstid = sst.sstid AND
  q.ssxid = ssx.ssxid AND
  q.dtid = d.dtid AND
  q.qtid = qt.qtid AND
  q.vrid = v.vrid

ORDER BY
  sx.sxid,
  ssx.ssxord,
  q.qord,
  qc.qcord

The double LEFT OUTER JOIN insures that I get every
question for a given section _and_ every
question_choice (if a multiple-choice question).

Now, I am trying to merge into this query, data from
the answers table (if it exists) based on the survey
id (surid), but still get _all_ of the questions and
questions_choices.  This is so I can re-display
incomplete forms.  Currently, I have only been able to
return information for those questions in a section
where answers do exist.  I.E. the final LEFT OUTER
JOIN is not including the previous JOIN's complete
results.


Query #2; query that does _not_ work:
SELECT
  sx.sxname, 
  sst.ssttext, 
  ssx.ssxused, 
  q.qid, 
  d.disptyp, 
  qt.qttext, 
  v.vrule, 
  qc.qcval, 
  c.ctext, 
  q.qrequired,
  a.aid,
  a.answer

FROM
  questions q LEFT OUTER JOIN questions_choices qc ON
q.qid = qc.qid
              LEFT OUTER JOIN choices c ON qc.cid =
c.cid
              LEFT OUTER JOIN answers a ON q.qid =
a.qid, 
  sections sx, 
  sub_sections ssx, 
  sub_sections_text sst, 
  disp_types d, 
  questions_text qt, 
  validation_rules v 

WHERE
  sx.sxid = $sxid AND       // $sxid is a run-time
variable (PHP) 
  a.surid = $surid AND      // $surid is a run-time
variable (PHP)
  ssx.sxid = sx.sxid AND 
  ssx.sstid = sst.sstid AND 
  q.ssxid = ssx.ssxid AND 
  q.dtid = d.dtid AND 
  q.qtid = qt.qtid AND 
  q.vrid = v.vrid 

ORDER BY
  sx.sxid, 
  ssx.ssxord, 
  q.qord, 
  qc.qcord

Any help will be greatly appreciated.  Sorry again
about the length of this message, and I will be happy
to provide any further clarification necessary!

Daniel

__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com

pgsql-novice by date

Next:From: Torbj=?ISO-8859-1?B?9g==?=rn AnderssonDate: 2001-12-06 16:32:13
Subject: Re: Storing number '001' ?
Previous:From: Charles HauserDate: 2001-12-05 23:33:26
Subject: Storing number '001' ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group