Antw: SQL Question

>>> 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)
referencesquestions(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 iwhere i.question = e.qid and i.quiznum =
some_quiznum_valueorderby 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
primarykeys 'by hand'.
 


Gerhard