From: | "Gerhard Dieringer" <DieringG(at)eba-haus(dot)de> |
---|---|
To: | <akrherz(at)iastate(dot)edu> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Antw: SQL Question |
Date: | 2000-08-02 07:32:40 |
Message-ID: | s987eace.039@kopo001 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
>>> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Jon Lapham | 2000-08-02 13:02:33 | Re: on line numbers, drop table errors, and log files |
Previous Message | Daryl Herzmann | 2000-08-01 20:38:13 | SQL Question |