Antw: SQL Question

>>> Daryl Herzmann <akrherz(at)iastate(dot)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