>>> Daryl Herzmann <[EMAIL PROTECTED]> 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
