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:

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

Show quoteHide quote

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.

Show quoteHide quote

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:

Show quoteHide quote

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:

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:

Show quoteHide quote

trigger doesn't seem to work w/cursor
Duplicate Column Names - A Good Trick to Avoid Temp Tables?
Primary Key, bigint or char?
release the Database Locks...
query on recursive table