question about a query

I've written this query:

select distinct topics, questions, answer
from topics AS A, QuesNans AS B
where A.topicid = B.topicid
order by a.topics

The results are:
Topic Questions Answers
Topic Four Question 1 Answer to question 1
Topic One Quesstion 2 Answer to question 2
Topic One Question 1 Answer to question 1
Topic Three Question 1 Answer to question 1
Topic Two Question 2 Answer to question 2

How do I get one topic listed but all of the questions that go with
that topic?

Re: question about a query

n Thu, 28 Jun 2007 12:44:17 -0700, nina297
wrote:

>I've written this query:
>
>select distinct topics, questions, answer
>from topics AS A, QuesNans AS B
>where A.topicid = B.topicid
>order by a.topics
>
>The results are:
>Topic Questions Answers
>Topic Four Question 1 Answer to question 1
>Topic One Quesstion 2 Answer to question 2
>Topic One Question 1 Answer to question 1
>Topic Three Question 1 Answer to question 1
>Topic Two Question 2 Answer to question 2
>
>How do I get one topic listed but all of the questions that go with
>that topic?

With a WHERE clause test? Or perhaps I do not understand the
question.

SELECT topic, question, answer
FROM Topics AS A
JOIN QuesNans AS B
ON A.topicid = B.topicid
WHERE A.topic = 'Topic One'
ORDER BY a.topic

By the way, it is common to make table names plural, and column names
singular. And it is much preferred to use the SQL-92 join syntax and
leave the WHERE clause for the rest tests to include/exclude rows (at
least in so far as they do not break OUTER joins.)

Roy Harvey
Beacon Falls, CT

Re: question about a query

nina297 (nina.childress@ssa.gov) writes:
> I've written this query:
>
> select distinct topics, questions, answer
> from topics AS A, QuesNans AS B
> where A.topicid = B.topicid
> order by a.topics
>
> The results are:
> Topic Questions Answers
> Topic Four Question 1 Answer to question 1
> Topic One Quesstion 2 Answer to question 2
> Topic One Question 1 Answer to question 1
> Topic Three Question 1 Answer to question 1
> Topic Two Question 2 Answer to question 2
>
> How do I get one topic listed but all of the questions that go with
> that topic?

So you get something like:

Topic Q1 A1 Q2 A2 ....
T Four Quest1 Ans1
T One Quest1 Ans2 Quest2 Ans2
....

If you know the maximum number of question per topics, you can do:

SELECT A.Topic,
Q1 = MIN (CASE n.n WHEN 1 THEN B.Questions END),
A1 = MIN (CASE n.n WHEN 1 THEN B.Answers END),
Q2 = MIN (CASE n.n WHEN 2 THEN B.Questions END),
A2 = MIN (CASE n.n WHEN 2 THEN B.Answers END),
...
Q5 = MIN (CASE n WHEN 5 THEN B.Questions END),
A5 = MIN (CASE n WHEN 5 THEN B.Answers END)
FROM Topics AS A
JOIN QuesNans AS B ON A.topicid = B.topicid
CROSS JOIN (SELECT 1 AS n UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5) AS n
GROUP BY A.Topic

This is a crosstab query. There are two "tricks". The first is the
derived table that generates the numbers 1 to 5. This is a query within
the query, which is a very useful technique, because the optimizer is
very good at recasting computation order for better performance. The
other is the MIN(CASE. The MIN here serves to get all one row, but the
MIN only sees one value. In fact MAX would work just as well.

If you cannot assume the maxmim number of questions per topic, you
need to build the query dynamically, which is quite an increase in
complexity. The third party tool RAC, at www4sql.rac.net is popular
for this.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: question about a query

On Jun 28, 4:32 pm, Erland Sommarskog wrote:
> nina297 (nina.childr...@ssa.gov) writes:
> > I've written this query:
>
> > select distinct topics, questions, answer
> > from topics AS A, QuesNans AS B
> > where A.topicid = B.topicid
> > order by a.topics
>
> > The results are:
> > Topic Questions Answers
> > Topic Four Question 1 Answer to question 1
> > Topic One Quesstion 2 Answer to question 2
> > Topic One Question 1 Answer to question 1
> > Topic Three Question 1 Answer to question 1
> > Topic Two Question 2 Answer to question 2
>
> > How do I get one topic listed but all of the questions that go with
> > that topic?
>
> So you get something like:
>
> Topic Q1 A1 Q2 A2 ....
> T Four Quest1 Ans1
> T One Quest1 Ans2 Quest2 Ans2
> ....
>
> If you know the maximum number of question per topics, you can do:
>
> SELECT A.Topic,
> Q1 = MIN (CASE n.n WHEN 1 THEN B.Questions END),
> A1 = MIN (CASE n.n WHEN 1 THEN B.Answers END),
> Q2 = MIN (CASE n.n WHEN 2 THEN B.Questions END),
> A2 = MIN (CASE n.n WHEN 2 THEN B.Answers END),
> ...
> Q5 = MIN (CASE n WHEN 5 THEN B.Questions END),
> A5 = MIN (CASE n WHEN 5 THEN B.Answers END)
> FROM Topics AS A
> JOIN QuesNans AS B ON A.topicid = B.topicid
> CROSS JOIN (SELECT 1 AS n UNION ALL
> SELECT 2 UNION ALL
> SELECT 3 UNION ALL
> SELECT 4 UNION ALL
> SELECT 5) AS n
> GROUP BY A.Topic
>
> This is a crosstab query. There are two "tricks". The first is the
> derived table that generates the numbers 1 to 5. This is a query within
> the query, which is a very useful technique, because the optimizer is
> very good at recasting computation order for better performance. The
> other is the MIN(CASE. The MIN here serves to get all one row, but the
> MIN only sees one value. In fact MAX would work just as well.
>
> If you cannot assume the maxmim number of questions per topic, you
> need to build the query dynamically, which is quite an increase in
> complexity. The third party tool RAC, at www4sql.rac.net is popular
> for this.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx- Hide quoted text -
>
> - Show quoted text -

Thanks so much for your help. I will try both options.