multiple joins

[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