From: Gerhard Dieringer <DieringG@eba-haus.de>
To: akrherz@iastate.edu
Cc: pgsql-sql@postgresql.org
Subject: Antw: SQL Question
Date: Wed, 02 Aug 2000 09:32:40 +0200
Message-ID: <s987eace.039@kopo001> (raw)
>>> Daryl Herzmann <akrherz@iastate.edu> 01.08.2000 22.38 Uhr >>>
> Hello,
> I hope this question is not too novice for this group.
>
> I have a table
>
> portfolio=# \d questions
> Table "questions"
> Attribute | Type | Modifier
> -----------+-------------+-----------------------------------------------------
> qid | integer | not null default nextval('questions_qid_seq'::text)
> optiona | text | default 'Z'
> optionb | text | default 'Z'
> optionc | text | default 'Z'
> optiond | text | default 'Z'
> optione | text | default 'Z'
> optionf | text | default 'Z'
> optiong | text | default 'Z'
> optionh | text | default 'Z'
>
>
> and other table named quizes, which contains references to the
> questions. Basically a quiz is composed of up to three questions.
>
> portfolio=# \d quizes
> Table "quizes"
> Attribute | Type | Modifier
> -----------+-------------+------------------------------------------------------
> quiznum | integer | not null default nextval('quizes_quiznum_seq'::text)
> question1 | integer |
> question2 | integer |
> question3 | integer |
>
>
>
> So my question is if I want to querry out a particular quiz and all its
> questions and question options, how can I do that in one command. I know
> that I could do it with a couple of loops, but I think the SQL is much
> more eliquent. Maybe I have my tables incorrectly set up? And
> suggestions?
>
>
> Thanks all,
> Daryl
I suggest to use the following structure:
create table quetions (
qid integer,
optid integer,
option text default 'Z'
primaray key (q_id,opt_id));
create table quizes (
quiznum integer,
question integer,
primary key (quiznum,question),
foreign key (question) references questions(qid));
I hope the syntax is ok, because I didn't check it.
Then your query is as simple as:
select i.quiznum, i.question, e.optid, e.option
from questions e, quizes i
where i.question = e.qid and i.quiznum = some_quiznum_value
order by 1,2,3;
Of course this has the drawback, that you can't use serials, but you explicitely have to use sequences and set the primary keys 'by hand'.
Gerhard
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: DieringG@eba-haus.de, akrherz@iastate.edu
Subject: Re: SQL Question
In-Reply-To: <s987eace.039@kopo001>
References: <s987eace.039@kopo001>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox