data design question

if I have a table to hold answer names such as
1 yes
2 no
3 maybe
and a table to hold question answers as the integer id value
question 1 - 1
question 2 - 3
question 3 - 2
question 4 - 1
to extract the literal represention i have to do
select
(select valuname from Answers where id = 1) as answer1,
(select valuname from Answers where id = 3) as answer2,
from
..etc
is there a name for this database design, and an easier way to extract
the data?By returning multiple answers as columns in the same row, you are building
what is known as a crosstab query.
<jw56578@.gmail.com> wrote in message
news:1139333386.478116.13650@.o13g2000cwo.googlegroups.com...
> if I have a table to hold answer names such as
> 1 yes
> 2 no
> 3 maybe
> and a table to hold question answers as the integer id value
> question 1 - 1
> question 2 - 3
> question 3 - 2
> question 4 - 1
> to extract the literal represention i have to do
> select
> (select valuname from Answers where id = 1) as answer1,
> (select valuname from Answers where id = 3) as answer2,
> from
> ...etc
>
> is there a name for this database design, and an easier way to extract
> the data?
>|||Please post your table structures, sample data & expected results so that
others can better understand your requirements. Based on your narrative, it
can be mostly accomplished using a series of CASE like:
SELECT <some group_identifier>
MAX( CASE id WHEN 1 THEN value END ) AS "answer1",
MAX( CASE id WHEN 1 THEN value END ) AS "answer1"
..
FROM tbl
GROUP BY <some group_identifier> ;
Anith|||You do this with JOIN
CREATE TABLE answers (answerid int NOT NULL, answerdesc varchar(10))
GO
ALTER TABLE answers ADD CONSTRAINT PK_answers_answerid PRIMARY KEY (answerid
)
GO
INSERT INTO answers VALUES (1,'yes')
INSERT INTO answers VALUES (2,'no')
INSERT INTO answers VALUES (3,'maybe')
CREATE TABLE questions (questionid int NOT NULL, answerid int NOT NULL)
GO
ALTER TABLE questions ADD CONSTRAINT PK_questions_questionid PRIMARY KEY
(questionid)
GO
ALTER TABLE questions ADD CONSTRAINT FK_questions_answerid FOREIGN KEY
(answerid)
REFERENCES answers (answerid)
GO
INSERT INTO questions VALUES (1,1)
INSERT INTO questions VALUES (2,3)
INSERT INTO questions VALUES (3,2)
INSERT INTO questions VALUES (4,1)
SELECT q.questionid, a.answerdesc
FROM questions q INNER JOIN answers a
ON q.answerid = a.answerid
"jw56578@.gmail.com" wrote:

> if I have a table to hold answer names such as
> 1 yes
> 2 no
> 3 maybe
> and a table to hold question answers as the integer id value
> question 1 - 1
> question 2 - 3
> question 3 - 2
> question 4 - 1
> to extract the literal represention i have to do
> select
> (select valuname from Answers where id = 1) as answer1,
> (select valuname from Answers where id = 3) as answer2,
> from
> ...etc
>
> is there a name for this database design, and an easier way to extract
> the data?
>