programming question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

programming question

Author

Topic

Kimmy
Starting Member

7 Posts

Posted - 2003-12-29 : 14:20:21

I am trying to write an output from my survey. I am not sure how I would go about writing this query or if I need to change my table structure. I want my out put to be like this:

Question 1 Count Answer
Subquestion
Choice A 1
Choice B 3
Question 2
Choice A 3
Choice B 1
Choice C 4
.
.
.

My current tables structure are:

TABLES:
SURVEY_QUESTION
QUESTION_ID PK VARCHAR(10)
QUESTION VARCHAR(100)

SURVEY_SUBQUESTION
SUBQUESTION_ID PK VARCHAR(10)
QUESTION_ID FK VARCHAR(10)
SUBQUESTION VARCHAR (200)

SURVEY_CHOICE
CHOICE_ID PK VARCHAR(10)
CHOICE VARCHAR(100)

SURVEY_QUESTION_CHOICE
QUESTION_ID FK VARCHAR(10)
CHOICE_ID FK VARCHAR(10)

SURVEY_ANSWER
USERID FK VARCHAR(10) , NOT NULL
QUESTION_ID FK VARCHAR(10), NOT NULL
SUBQUESTION_ID FK VARCHAR(10), NULL
CHOICE_ID FK VARCHAR(10), NOT NULL
ANSWER TEXT

Any help will be appreciated. Thanks, Kim

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-29 : 14:31:08

What do you mean by "writing an output" ? Something on a web page, or a report , or something in Excel ? How would you like to present these results to the users?

- Jeff

Kimmy
Starting Member

7 Posts

Posted - 2003-12-30 : 07:32:07

I would like to write out the output on the web page.

quote:
Originally posted by jsmith8858

What do you mean by "writing an output" ? Something on a web page, or a report , or something in Excel ? How would you like to present these results to the users?

- Jeff

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-12-30 : 07:48:09

Using what ?

ASP ? ASP.NET ? JSP ? PHP ? Perl ? Python ? Ruby ?

Damian

Kimmy
Starting Member

7 Posts

Posted - 2003-12-30 : 11:27:17

I am using ASP and SQL Server 2000

quote:
Originally posted by Merkin

Using what ?

ASP ? ASP.NET ? JSP ? PHP ? Perl ? Python ? Ruby ?

Damian

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-30 : 12:20:33

Do you need help with writing the SQL or generating the web page or both?

- Jeff

Kimmy
Starting Member

7 Posts

Posted - 2003-12-30 : 12:33:58

I just need to need help writing the SQL part. This is what I have so far, but I don't want the questions to be repeated so many times

SELECT QUESTION, SUBQUESTION,CHOICE, COUNT(*) AS COUNT_
FROM SURVEY_ANSWER
JOIN SURVEY_QUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION.QUESTION_ID
LEFT OUTER JOIN SURVEY_SUBQUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_SUBQUESTION.QUESTION_ID
AND SURVEY_ANSWER.SUBQUESTION_ID = SURVEY_SUBQUESTION.SUBQUESTION_ID
JOIN SURVEY_QUESTION_CHOICE ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION_CHOICE.QUESTION_ID
AND SURVEY_ANSWER.CHOICE_ID = SURVEY_QUESTION_CHOICE.CHOICE_ID
JOIN SURVEY_CHOICE ON SURVEY_QUESTION_CHOICE.CHOICE_ID = SURVEY_CHOICE.CHOICE_ID
GROUP BY SURVEY_QUESTION.QUESTION,SURVEY_SUBQUESTION.SUBQUESTION,SURVEY_CHOICE.CHOICE

SELECT 'QUESTION 1' AS QUESTION_NUM, QUESTION, NULL AS SUBQUESTION, CHOICE, COUNT(*) AS COUNT_ANSWER
FROM SURVEY_ANSWER
JOIN SURVEY_QUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION.QUESTION_ID
LEFT OUTER JOIN SURVEY_SUBQUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_SUBQUESTION.QUESTION_ID
AND SURVEY_ANSWER.SUBQUESTION_ID = SURVEY_SUBQUESTION.SUBQUESTION_ID
JOIN SURVEY_QUESTION_CHOICE ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION_CHOICE.QUESTION_ID
AND SURVEY_ANSWER.CHOICE_ID = SURVEY_QUESTION_CHOICE.CHOICE_ID
JOIN SURVEY_CHOICE ON SURVEY_QUESTION_CHOICE.CHOICE_ID = SURVEY_CHOICE.CHOICE_ID
WHERE SURVEY_ANSWER.QUESTION_ID = 'Q01'
GROUP BY SURVEY_QUESTION.QUESTION, SUBQUESTION, SURVEY_CHOICE.CHOICE
UNION ALL
SELECT 'QUESTION 2' AS QUESTION_NUM, QUESTION, NULL AS SUBQUESTION, CHOICE, COUNT(*) AS COUNT_ANSWER
FROM SURVEY_ANSWER
JOIN SURVEY_QUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION.QUESTION_ID
LEFT OUTER JOIN SURVEY_SUBQUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_SUBQUESTION.QUESTION_ID
AND SURVEY_ANSWER.SUBQUESTION_ID = SURVEY_SUBQUESTION.SUBQUESTION_ID
JOIN SURVEY_QUESTION_CHOICE ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION_CHOICE.QUESTION_ID
AND SURVEY_ANSWER.CHOICE_ID = SURVEY_QUESTION_CHOICE.CHOICE_ID
JOIN SURVEY_CHOICE ON SURVEY_QUESTION_CHOICE.CHOICE_ID = SURVEY_CHOICE.CHOICE_ID
WHERE SURVEY_ANSWER.QUESTION_ID = 'Q02'
GROUP BY SURVEY_QUESTION.QUESTION, SUBQUESTION, SURVEY_CHOICE.CHOICE
UNION ALL
SELECT 'QUESTION 3' AS QUESTION_NUM,QUESTION, NULL AS SUBQUESTION, CHOICE, COUNT(*) AS COUNT_ANSWER
FROM SURVEY_ANSWER
JOIN SURVEY_QUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION.QUESTION_ID
LEFT OUTER JOIN SURVEY_SUBQUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_SUBQUESTION.QUESTION_ID
AND SURVEY_ANSWER.SUBQUESTION_ID = SURVEY_SUBQUESTION.SUBQUESTION_ID
JOIN SURVEY_QUESTION_CHOICE ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION_CHOICE.QUESTION_ID
AND SURVEY_ANSWER.CHOICE_ID = SURVEY_QUESTION_CHOICE.CHOICE_ID
JOIN SURVEY_CHOICE ON SURVEY_QUESTION_CHOICE.CHOICE_ID = SURVEY_CHOICE.CHOICE_ID
WHERE SURVEY_ANSWER.QUESTION_ID = 'Q03'
GROUP BY SURVEY_QUESTION.QUESTION, SUBQUESTION, SURVEY_CHOICE.CHOICE
UNION ALL
SELECT 'QUESTION 4' AS QUESTION_NUM,QUESTION, NULL AS SUBQUESTION, CHOICE, COUNT(*) AS COUNT_ANSWER
FROM SURVEY_ANSWER
JOIN SURVEY_QUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION.QUESTION_ID
LEFT OUTER JOIN SURVEY_SUBQUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_SUBQUESTION.QUESTION_ID
AND SURVEY_ANSWER.SUBQUESTION_ID = SURVEY_SUBQUESTION.SUBQUESTION_ID
JOIN SURVEY_QUESTION_CHOICE ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION_CHOICE.QUESTION_ID
AND SURVEY_ANSWER.CHOICE_ID = SURVEY_QUESTION_CHOICE.CHOICE_ID
JOIN SURVEY_CHOICE ON SURVEY_QUESTION_CHOICE.CHOICE_ID = SURVEY_CHOICE.CHOICE_ID
WHERE SURVEY_ANSWER.QUESTION_ID = 'Q04'
GROUP BY SURVEY_QUESTION.QUESTION, SUBQUESTION, SURVEY_CHOICE.CHOICE
UNION ALL
SELECT 'QUESTION 4A' AS QUESTION_NUM,QUESTION, SUBQUESTION, CHOICE, COUNT(*) AS COUNT_ANSWER
FROM SURVEY_ANSWER
JOIN SURVEY_QUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION.QUESTION_ID
LEFT OUTER JOIN SURVEY_SUBQUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_SUBQUESTION.QUESTION_ID
AND SURVEY_ANSWER.SUBQUESTION_ID = SURVEY_SUBQUESTION.SUBQUESTION_ID
JOIN SURVEY_QUESTION_CHOICE ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION_CHOICE.QUESTION_ID
AND SURVEY_ANSWER.CHOICE_ID = SURVEY_QUESTION_CHOICE.CHOICE_ID
JOIN SURVEY_CHOICE ON SURVEY_QUESTION_CHOICE.CHOICE_ID = SURVEY_CHOICE.CHOICE_ID
WHERE SURVEY_ANSWER.QUESTION_ID = 'Q04A'
GROUP BY SURVEY_QUESTION.QUESTION, SUBQUESTION, SURVEY_CHOICE.CHOICE
UNION ALL
SELECT 'QUESTION 5' AS QUESTION_NUM,QUESTION, NULL AS SUBQUESTION, CHOICE, COUNT(*) AS COUNT_ANSWER
FROM SURVEY_ANSWER
JOIN SURVEY_QUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION.QUESTION_ID
LEFT OUTER JOIN SURVEY_SUBQUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_SUBQUESTION.QUESTION_ID
AND SURVEY_ANSWER.SUBQUESTION_ID = SURVEY_SUBQUESTION.SUBQUESTION_ID
JOIN SURVEY_QUESTION_CHOICE ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION_CHOICE.QUESTION_ID
AND SURVEY_ANSWER.CHOICE_ID = SURVEY_QUESTION_CHOICE.CHOICE_ID
JOIN SURVEY_CHOICE ON SURVEY_QUESTION_CHOICE.CHOICE_ID = SURVEY_CHOICE.CHOICE_ID
WHERE SURVEY_ANSWER.QUESTION_ID = 'Q05'
GROUP BY SURVEY_QUESTION.QUESTION, SUBQUESTION, SURVEY_CHOICE.CHOICE
UNION ALL
SELECT 'QUESTION 6' AS QUESTION_NUM,QUESTION, NULL AS SUBQUESTION, CHOICE, COUNT(*) AS COUNT_ANSWER
FROM SURVEY_ANSWER
JOIN SURVEY_QUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION.QUESTION_ID
LEFT OUTER JOIN SURVEY_SUBQUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_SUBQUESTION.QUESTION_ID
AND SURVEY_ANSWER.SUBQUESTION_ID = SURVEY_SUBQUESTION.SUBQUESTION_ID
JOIN SURVEY_QUESTION_CHOICE ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION_CHOICE.QUESTION_ID
AND SURVEY_ANSWER.CHOICE_ID = SURVEY_QUESTION_CHOICE.CHOICE_ID
JOIN SURVEY_CHOICE ON SURVEY_QUESTION_CHOICE.CHOICE_ID = SURVEY_CHOICE.CHOICE_ID
WHERE SURVEY_ANSWER.QUESTION_ID = 'Q06'
GROUP BY SURVEY_QUESTION.QUESTION, SUBQUESTION, SURVEY_CHOICE.CHOICE
UNION ALL
SELECT 'QUESTION 7' AS QUESTION_NUM,QUESTION, SUBQUESTION, CHOICE, COUNT(*) AS COUNT_ANSWER
FROM SURVEY_ANSWER
JOIN SURVEY_QUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION.QUESTION_ID
LEFT OUTER JOIN SURVEY_SUBQUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_SUBQUESTION.QUESTION_ID
AND SURVEY_ANSWER.SUBQUESTION_ID = SURVEY_SUBQUESTION.SUBQUESTION_ID
JOIN SURVEY_QUESTION_CHOICE ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION_CHOICE.QUESTION_ID
AND SURVEY_ANSWER.CHOICE_ID = SURVEY_QUESTION_CHOICE.CHOICE_ID
JOIN SURVEY_CHOICE ON SURVEY_QUESTION_CHOICE.CHOICE_ID = SURVEY_CHOICE.CHOICE_ID
WHERE SURVEY_ANSWER.QUESTION_ID = 'Q07'
GROUP BY SURVEY_QUESTION.QUESTION, SUBQUESTION, SURVEY_CHOICE.CHOICE
UNION ALL
SELECT 'QUESTION 8' AS QUESTION_NUM,QUESTION, SUBQUESTION, CHOICE, COUNT(*) AS COUNT_ANSWER
FROM SURVEY_ANSWER
JOIN SURVEY_QUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION.QUESTION_ID
LEFT OUTER JOIN SURVEY_SUBQUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_SUBQUESTION.QUESTION_ID
AND SURVEY_ANSWER.SUBQUESTION_ID = SURVEY_SUBQUESTION.SUBQUESTION_ID
JOIN SURVEY_QUESTION_CHOICE ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION_CHOICE.QUESTION_ID
AND SURVEY_ANSWER.CHOICE_ID = SURVEY_QUESTION_CHOICE.CHOICE_ID
JOIN SURVEY_CHOICE ON SURVEY_QUESTION_CHOICE.CHOICE_ID = SURVEY_CHOICE.CHOICE_ID
WHERE SURVEY_ANSWER.QUESTION_ID = 'Q08'
GROUP BY SURVEY_QUESTION.QUESTION, SUBQUESTION, SURVEY_CHOICE.CHOICE
UNION ALL
SELECT 'QUESTION 9' AS QUESTION_NUM,QUESTION, NULL AS SUBQUESTION, CHOICE, COUNT(*) AS COUNT_ANSWER
FROM SURVEY_ANSWER
JOIN SURVEY_QUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION.QUESTION_ID
LEFT OUTER JOIN SURVEY_SUBQUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_SUBQUESTION.QUESTION_ID
AND SURVEY_ANSWER.SUBQUESTION_ID = SURVEY_SUBQUESTION.SUBQUESTION_ID
JOIN SURVEY_QUESTION_CHOICE ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION_CHOICE.QUESTION_ID
AND SURVEY_ANSWER.CHOICE_ID = SURVEY_QUESTION_CHOICE.CHOICE_ID
JOIN SURVEY_CHOICE ON SURVEY_QUESTION_CHOICE.CHOICE_ID = SURVEY_CHOICE.CHOICE_ID
WHERE SURVEY_ANSWER.QUESTION_ID = 'Q09'
GROUP BY SURVEY_QUESTION.QUESTION, SUBQUESTION, SURVEY_CHOICE.CHOICE
UNION ALL
SELECT 'QUESTION 10' AS QUESTION_NUM,QUESTION, SUBQUESTION, CHOICE, COUNT(*) AS COUNT_ANSWER
FROM SURVEY_ANSWER
JOIN SURVEY_QUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION.QUESTION_ID
LEFT OUTER JOIN SURVEY_SUBQUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_SUBQUESTION.QUESTION_ID
AND SURVEY_ANSWER.SUBQUESTION_ID = SURVEY_SUBQUESTION.SUBQUESTION_ID
JOIN SURVEY_QUESTION_CHOICE ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION_CHOICE.QUESTION_ID
AND SURVEY_ANSWER.CHOICE_ID = SURVEY_QUESTION_CHOICE.CHOICE_ID
JOIN SURVEY_CHOICE ON SURVEY_QUESTION_CHOICE.CHOICE_ID = SURVEY_CHOICE.CHOICE_ID
WHERE SURVEY_ANSWER.QUESTION_ID = 'Q10'
GROUP BY SURVEY_QUESTION.QUESTION, SUBQUESTION, SURVEY_CHOICE.CHOICE

QUESTION_NUM QUESTION SUBQUESTION CHOICES COUNT
QUESTION 1 HOW DID YOU HEAR ABOUT US? NULL ASK JEEVES 2
QUESTION 1 HOW DID YOU HEAR ABOUT US? NULL GOOGLE 5
QUESTION 2 WHAT DO YOU THINK ABOUT TRAINING LOCATION GREAT 1
QUESTION 2 WHAT DO YOU THINK ABOUT TRAINING LOCATION ADEQUATE 1
QUESTION 2 WHAT DO YOU THINK ABOUT TRAINING QUALITY THE BEST 1
QUESTION 2 WHAT DO YOU THINK ABOUT TRAINING QUALITY AVERAGE 1
.
.
.

quote:
Originally posted by jsmith8858

Do you need help with writing the SQL or generating the web page or both?

- Jeff

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-30 : 12:38:35

Assuming each answer is different for each record you will end up with questions repeated for each distinct answer.
If you are trying to aggregate the count of choices for each subquestion wihtout each answer you will not have this problem.

Kimmy
Starting Member

7 Posts

Posted - 2003-12-30 : 13:03:04

I'm trying to show each questions and subquestion and the count for each. Not every questions have a subquestions. This is what I would like it to display.

QUESTION_NUM QUESTION SUBQUESTION CHOICES COUNT
QUESTION 1 HOW DID YOU HEAR ABOUT US? NULL ASK JEEVES 2
GOOGLE 5

QUESTION 2 WHAT DO YOU THINK ABOUT TRAINING LOCATION GREAT 1
LOCATION ADEQUATE 1
QUALITY THE BEST 1
QUALITY AVERAGE 1

quote:
Originally posted by ehorn

Assuming each answer is different for each record you will end up with questions repeated for each distinct answer.
If you are trying to aggregate the count of choices for each subquestion wihtout each answer you will not have this problem.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-30 : 13:25:39

Kimmy --

please give us some INSERT statements and CREATE TABLE statements and some sample data, and then what you want to display or return. I am having a hard time understanding what you are telling us you want to display.

- Jeff

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-30 : 13:40:40

I believe the following query will give you the results you are looking for but you will need to perform logic in the presentation tier to format the resultset into your desired output:

select d.question_id question_num, sq.question, d.subquestion_id, ss.subquestion, sc.choice choices, d.choice_count count
from 
(
select question_id,subquestion_id,choice_id,count(choice_id) as choice_count
from survey_answer
group by question_id,subquestion_id, choice_id
) d 
join survey_question sq on sq.question_id = d.question_id
join survey_subquestion ss on ss.subquestion_id = d.subquestion_id and ss.question_id = d.question_id
join survey_choice sc on sc.choice_id = d.choice_id
order by d.question_id,d.subquestion_id, d.choice_id

Kimmy
Starting Member

7 Posts

Posted - 2003-12-31 : 07:40:00

Thanks ehorn. What you wrote give me the same result as my query:

SELECT SURVEY_ANSWER.QUESTION_ID,QUESTION, SUBQUESTION,CHOICE, COUNT(*) AS COUNT_
FROM SURVEY_ANSWER
JOIN SURVEY_QUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION.QUESTION_ID
LEFT OUTER JOIN SURVEY_SUBQUESTION ON SURVEY_ANSWER.QUESTION_ID = SURVEY_SUBQUESTION.QUESTION_ID
AND SURVEY_ANSWER.SUBQUESTION_ID = SURVEY_SUBQUESTION.SUBQUESTION_ID
JOIN SURVEY_QUESTION_CHOICE ON SURVEY_ANSWER.QUESTION_ID = SURVEY_QUESTION_CHOICE.QUESTION_ID
AND SURVEY_ANSWER.CHOICE_ID = SURVEY_QUESTION_CHOICE.CHOICE_ID
JOIN SURVEY_CHOICE ON SURVEY_QUESTION_CHOICE.CHOICE_ID = SURVEY_CHOICE.CHOICE_ID
GROUP BY SURVEY_ANSWER.QUESTION_ID,SURVEY_QUESTION.QUESTION,SURVEY_SUBQUESTION.SUBQUESTION,SURVEY_CHOICE.CHOICE

I apologize for not explaining what I want clearly. I just want he results display in hierarchy in ASP side...like a tree view.

Question Number (Question One)

-------Question (What do you think about our training?)

-----------Subquestion (Topics Cover)--not every ? has a subquestion

--------------Choices (Needs Improvement) 3 (this number counts how many times it has been answered)

--------------Choices (Average) 2 (this number counts how many times it has been answered)

e.g.

QUESTION_NUM

QUESTION 1

-------QUESTION

-------How did you hear about us?

---------SUBQUESTION

----------NULL

-----------CHOICES

------------ASK JEEVES 2

------------GOOGLE 5

QUESTION_NUM

QUESTION 2

-------QUESTION

-------What do you think about training?

---------SUBQUESTION

---------Topics Cover

-----------CHOICES

------------GREAT 2

------------AVERAGE 5

---------SUBQUESTION

---------Quality

-----------CHOICES

------------ADEQUATE 2

------------THE BEST 5

quote:

Originally posted by ehorn

I believe the following query will give you the results you are looking for but you will need to perform logic in the presentation tier to format the resultset into your desired output:

select d.question_id question_num, sq.question, d.subquestion_id, ss.subquestion, sc.choice choices, d.choice_count count
from 
(
select question_id,subquestion_id,choice_id,count(choice_id) as choice_count
from survey_answer
group by question_id,subquestion_id, choice_id
) d 
join survey_question sq on sq.question_id = d.question_id
join survey_subquestion ss on ss.subquestion_id = d.subquestion_id and ss.question_id = d.question_id
join survey_choice sc on sc.choice_id = d.choice_id
order by d.question_id,d.subquestion_id, d.choice_id

mr_mist
Grunnio

1870 Posts

Posted - 2004-01-05 : 03:30:59

If you want it displayed in a tree, then you may be making life hard for yourself by trying to extract the information all in one go.

I'd do seperate queries for all the possible answers, sub-questions and questions, and store them all in arrays at the application end. Then, assuming you have suitable foreign keys, you should be able to build the menu tree up from the information you have in each array.

Yes.

-------
Moo. :)

Kimmy
Starting Member

7 Posts

Posted - 2004-01-07 : 06:11:33

Thank you for everyone's help. I found a way on the ASP to display the way I want

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources