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