What is wrong with this View? - 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)

What is wrong with this View?

Author

Topic

scripter
Starting Member

49 Posts

Posted - 2006-11-13 : 02:18:41

Error
Unable to parse query text.

SELECT Q.*, A.answercount
FROM (SELECT count(*)
FROM answers a
WHERE a.questionid = answers.questionid AND a.quizid = answers.quizid) AS count
FROM question Q INNER JOIN
(SELECT questionid, quizid, count(*) answercount
FROM answers
GROUP BY questionid, quizid) A ON A.questionid = Q.questionid AND A.quizid = Q.quizid

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-13 : 02:21:58

[code]SELECT Q.*,
A.answercount
FROM (
SELECT count(*)
FROM answers a
WHERE a.questionid = answers.questionid AND a.quizid = answers.quizid
) AS count
FROM question Q
INNER JOIN (
SELECT questionid,
quizid,
count(*) answercount
FROM answers
GROUP BY questionid,
quizid
) A ON A.questionid = Q.questionid AND A.quizid = Q.quizid[/code]

Peter Larsson
Helsingborg, Sweden

scripter
Starting Member

49 Posts

Posted - 2006-11-13 : 02:30:42

do you know anything about ASP.NET?

Kristen
Test

22859 Posts

Posted - 2006-11-13 : 02:32:23

Some people here do, there's even a forum:

http://www.sqlteam.com/forums/forum.asp?FORUM_ID=17

Kristen

scripter
Starting Member

49 Posts

Posted - 2006-11-13 : 02:37:19

well wait hold up for a minute reason why I was asking was well the View or Stored Procedure that I am needing needs to have everything for each quiz to appear on one single row...

http://shotdrive.com/hopefulView.JPG

If you take a look at this screenshot you can see what the two tables look like. all I really need out of table Answers is the field "Answer" which that filed will be repeated multiple times for each question there is.
with ASP.Net a Gridview or DetailsView needs to have records that appear as one single row

Is there away to do this. I can provide the quizid and or questionid during runtime if needed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-13 : 02:40:43

Oh Brother...

http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-13 : 02:42:27

All of a sudden, this topic is tranferred to here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74825

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-11-13 : 04:21:02

"All of a sudden, this topic is tranferred to here"

Well, I did mention that there was an ASP.NET forum around these parts ...