Thread

Antw: SQL Question

Gerhard Dieringer <dieringg@eba-haus.de> — 2000-08-02T07:32:40Z

>>> 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