Hierarchical Result Set!

I have the following table structures.
question
- question
- text
question_opts
- question_opt_id
- opt_text
One question can have many options.
When i want to retrieve a result set containing a question with all its
options
i get a copy of the question with each option retrieved.
Is there someway to get around this. Can i retrieve the data in a more
hierarchial fashion.
so it comes back like:
questions.question_id
questions.text
option_1
option_2
ect.
Help appreciated!Here's a sample of one way you can format your output using CASE:
SELECT CASE s.rank WHEN 1 THEN CAST(s.question_id AS VARCHAR) ELSE '' END AS
q_id,
CASE s.rank WHEN 1 THEN q.question_text ELSE '' END AS q_text,
s.question_opt_id AS o_id,
s.opt_text AS o_text
FROM questions q INNER JOIN
(
SELECT rank=COUNT(*), q.question_id, o.question_opt_id, o.opt_text
FROM questions q, question_opts o, question_opts o2
WHERE q.question_id = o.question_id
AND o.question_id = o2.question_id
AND o.question_opt_id <= o2.question_opt_id
GROUP BY q.question_id, o.question_opt_id, o.opt_text
) s
ON q.question_id = s.question_id
GROUP BY s.question_id, s.rank, q.question_text, s.question_opt_id,
s.opt_text
ORDER BY s.question_id, s.rank
Here's the output:
+--+--+--+--+
| q_id | q_text | o_id | o_text |
+--+--+--+--+
| 1 | What color is grass ? | 4 | Red |
| | | 3 | Black |
| | | 2 | Green |
| | | 1 | Blue |
| 2 | What is 2 + 2 ? | 4 | 22 |
| | | 3 | 4 |
| | | 2 | 2 |
| | | 1 | 1 |
+--+--+--+--+
If it looks screwy in your newsreader, paste it into NotePad using a
fixed-width font. It's not exactly what you asked for, but it does return
each question only once, followed by it's related options.
"AJ" <AJ@.discussions.microsoft.com> wrote in message
news:C8374610-B9C1-492F-8BB6-184D5AD9893D@.microsoft.com...
>I have the following table structures.
> question
> - question
> - text
> question_opts
> - question_opt_id
> - opt_text
> One question can have many options.
> When i want to retrieve a result set containing a question with all its
> options
> i get a copy of the question with each option retrieved.
> Is there someway to get around this. Can i retrieve the data in a more
> hierarchial fashion.
> so it comes back like:
> questions.question_id
> questions.text
> option_1
> option_2
> ect.
> Help appreciated!
>|||P.S. - another way is to return 2 result sets, one selecting from the
question table; the second selecting from the related question_id's in the
question_opts table.
"AJ" <AJ@.discussions.microsoft.com> wrote in message
news:C8374610-B9C1-492F-8BB6-184D5AD9893D@.microsoft.com...
>I have the following table structures.
> question
> - question
> - text
> question_opts
> - question_opt_id
> - opt_text
> One question can have many options.
> When i want to retrieve a result set containing a question with all its
> options
> i get a copy of the question with each option retrieved.
> Is there someway to get around this. Can i retrieve the data in a more
> hierarchial fashion.
> so it comes back like:
> questions.question_id
> questions.text
> option_1
> option_2
> ect.
> Help appreciated!
>|||Or derive the UNION ALL of two result sets, along with additional
positioning columns,
and then select from that with the appropriate ORDER BY clause. Here's
an example:
select CustomerID, OrderID from (
select
CustomerID as Position_1,
1 as Position_2,
CustomerID,
cast(min(OrderID) as varchar(15)) as OrderID
from Northwind..Orders
group by CustomerID
union all
select
CustomerID as Position_1,
2 as Position_2,
'',
cast(OrderID as varchar(15)) as OrderID
from Northwind..Orders as O1
where OrderID <> (
select min(O2.OrderID)
from Northwind..Orders as O2
where O2.CustomerID = O1.CustomerID
)
union all
select distinct
CustomerID,
3,
'',
N''
from Northwind..Orders
) Report
order by Position_1, Position_2
go
-- Steve Kass
-- Drew University
Michael C# wrote:

>P.S. - another way is to return 2 result sets, one selecting from the
>question table; the second selecting from the related question_id's in the
>question_opts table.
>"AJ" <AJ@.discussions.microsoft.com> wrote in message
>news:C8374610-B9C1-492F-8BB6-184D5AD9893D@.microsoft.com...
>
>
>|||I am assuming a previous post did not work.
Steve's approach looks like what i am after.
However, unfortunately it is a little over my head. I would appreciate it if
some one could break it down for me.
My attempt at my current problem so far is:
SELECT assmnt_question_id, qtext FROM assmnt_questions
WHERE assmnt_question_id = 1
UNION ALL
SELECT assmnt_quest_opt_id, opt_text FROM assmnt_quest_opts
WHERE assmnt_question_id = 1
It goes some way to achieving what i am after, however the assmnt_questions
data is sharing the same columns as the assmnt_quest_opts data..just a row
below.
Would prefer that this was not the case!|||AJ,
You are selecting two columns here, and so your result will only
have two columns... You need to put extra columns in, for starters.
This is
all untested, but I hope it gives you the idea.
You want header rows:
1 | What color is grass ? | 4 | Red
select
Q.assmnt_question_id,
Q.qtext,
P.assmnt_quest_opt_id,
P.opt_text
from assmnt_questions as Q
join assmnt_quest_opts as P
on P.assmnt_question_id = Q.assmnt_question_id
where P.assmnt_quest_opt_id = (
select max(assmnt_quest_opt_id)
from assmnt_quest_opts as P2
where P2.assmnt_question_id = Q.assmnt_question_id
)
and you want other rows:
| | | 3 | Black |
select
'' as assmnt_question_id,
'' as qtext,
P.assmnt_quest_opt_id,
P.opt_text
from assmnt_quest_opts as P
where P.assmnt_question_id < (
select max(assmnt_quest_opt_id)
from assmnt_quest_opts as P2
where P2.assmnt_question_id = P.assmnt_question_id
)
And you need to cast the integers in columns where '' is needed,
and add columns for ordering, when you UNION ALL these:
select
cast(Q.assmnt_question_id as varchar(10)) as assmnt_question_id,
Q.qtext,
P.assmnt_quest_opt_id,
P.opt_text,
Q.assmnt_question_id as OuterOrderValue,
P.assmnt_quest_opt_id as InnerOrderValue
from assmnt_questions as Q
join assmnt_quest_opts as P
on P.assmnt_question_id = Q.assmnt_question_id
where P.assmnt_quest_opt_id = (
select max(assmnt_quest_opt_id)
from assmnt_quest_opts as P2
where P2.assmnt_question_id = Q.assmnt_question_id
)
union all
select
'',
'',
P.assmnt_quest_opt_id,
P.opt_text,
P.assmnt_question_id,
P.assmnt_quest_opt_id
from assmnt_quest_opts as P
where P.assmnt_question_id < (
select max(assmnt_quest_opt_id)
from assmnt_quest_opts as P2
where P2.assmnt_question_id = P.assmnt_question_id
)
Then you select the columns you want, ordering by the ordering
columns:
select
assmnt_question_id,
qtext,
assmnt_quest_opt_id,
opt_text
from (
select * from (
select
cast(Q.assmnt_question_id as varchar(10)) as assmnt_question_id,
Q.qtext,
P.assmnt_quest_opt_id,
P.opt_text,
Q.assmnt_question_id as OuterOrderValue,
P.assmnt_quest_opt_id as InnerOrderValue
from assmnt_questions as Q
join assmnt_quest_opts as P
on P.assmnt_question_id = Q.assmnt_question_id
where P.assmnt_quest_opt_id = (
select max(assmnt_quest_opt_id)
from assmnt_quest_opts as P2
where P2.assmnt_question_id = Q.assmnt_question_id
)
union all
select
'',
'',
P.assmnt_quest_opt_id,
P.opt_text,
P.assmnt_question_id,
P.assmnt_quest_opt_id
from assmnt_quest_opts as P
where P.assmnt_question_id < (
select max(assmnt_quest_opt_id)
from assmnt_quest_opts as P2
where P2.assmnt_question_id = P.assmnt_question_id
)
) E
order by OuterOrderValue, InnerOrderValue desc
There is a bit of quirkiness about combining UNION
and ORDER BY, and you may need to add another
select * from (
the select query I suggest
) as T
somewhere. I forget when you have to do that.
SK
AJ wrote:

>I am assuming a previous post did not work.
>Steve's approach looks like what i am after.
>However, unfortunately it is a little over my head. I would appreciate it i
f
>some one could break it down for me.
>My attempt at my current problem so far is:
>SELECT assmnt_question_id, qtext FROM assmnt_questions
>WHERE assmnt_question_id = 1
>UNION ALL
>SELECT assmnt_quest_opt_id, opt_text FROM assmnt_quest_opts
>WHERE assmnt_question_id = 1
>It goes some way to achieving what i am after, however the assmnt_questions
>data is sharing the same columns as the assmnt_quest_opts data..just a row
>below.
>Would prefer that this was not the case!
>