Received: from elch.de.uu.net (elch.de.uu.net [192.76.144.55]) by hub.org (8.10.1/8.10.1) with ESMTP id e727XvB53036 for ; Wed, 2 Aug 2000 03:34:01 -0400 (EDT) Received: from kopo001 ([149.228.241.248]) by elch.de.uu.net (5.5.5/5.5.5) with SMTP id JAA07424 for ; Wed, 2 Aug 2000 09:30:23 +0200 (MET DST) Received: from EBA_DO-Message_Server by kopo001 with Novell_GroupWise; Wed, 02 Aug 2000 09:33:02 +0200 Message-Id: X-Mailer: Novell GroupWise 5.5.2 Date: Wed, 02 Aug 2000 09:32:40 +0200 From: "Gerhard Dieringer" To: Cc: Subject: Antw: SQL Question Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Disposition: inline Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id e727Y1B53053 X-Archive-Number: 200008/7 >>> Daryl Herzmann 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