pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gerhard Dieringer <DieringG@eba-haus.de>
To: akrherz@iastate.edu
Cc: pgsql-sql@postgresql.org
Subject: Antw: SQL Question
Date: Wed, 02 Aug 2000 09:32:40 +0200
Message-ID: <s987eace.039@kopo001> (raw)

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








Message-ID: <s987eace.039@kopo001>
Permalink:  ../s987eace.039@kopo001/
Also on:    postgresql.org/message-id/s987eace.039@kopo001

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: DieringG@eba-haus.de, akrherz@iastate.edu
  Subject: Re: SQL Question
  In-Reply-To: <s987eace.039@kopo001>
  References: <s987eace.039@kopo001>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox