T-SQL Query help needed! - 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)

T-SQL Query help needed!

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-03-16 : 09:40:36

I have three tables which I have listed below. Now I want to select all the questions of ExamID = 1 and their respective choices. When I run my query it returns something like this:

1 Dummy Exam 1 This is the Dummy Exam. 60 3 1) Which is you name? a) <img src='images/t3_f05_problem02__2.gif' align='center'> 1
1 Dummy Exam 1 This is the Dummy Exam. 60 3 1) Which is you name? b) <img src='images/t3_f05_problem02__3.gif' align='center'> 1
1 Dummy Exam 1 This is the Dummy Exam. 60 3 1) Which is you name? c) <img src='images/t3_f05_problem02__4.gif' align='center'> 1
1 Dummy Exam 1 This is the Dummy Exam. 60 3 1) Which is you name? d) <img src='images/t3_f05_problem02__5.gif' align='center'> 1
1 Dummy Exam 1 This is the Dummy Exam. 60 3 1) Which is you name? e) <img src='images/t3_f05_problem02__6.gif' align='center'> 1
1 Dummy Exam 1 This is the Dummy Exam. 60 3 1) Which is you name? f) None of the above. 1

So, as you can see the data is repeating. In the above data the image tags which are the choices are getting out differently which is okay. I was just wondering what can I do to improve this. Since each Exam has many questions and each question has many choices.

CREATE TABLE [dbo].[test_ExamChoices] (
[ExamChoiceID] [int] IDENTITY (1, 1) NOT NULL ,
[ExamQuestionID] [int] NOT NULL ,
[ChoiceText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[test_ExamQuestions] (
[ExamQuestionID] [int] IDENTITY (1, 1) NOT NULL ,
[ExamID] [int] NULL ,
[QuestionText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Status] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[test_Exams] (
[ExamID] [int] IDENTITY (1, 1) NOT NULL ,
[ExamTypeID] [int] NOT NULL ,
[Title] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ExamDateID] [int] NOT NULL ,
[Duration] [int] NOT NULL ,
[TotalQuestions] [int] NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[DateModified] [datetime] NOT NULL ,
[ExamStatusID] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Mohammad Azam
www.azamsharp.net

mr_mist
Grunnio

1870 Posts

Posted - 2006-03-16 : 09:42:26

What query are you using at the moment?

-------
Moo. :)

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-03-16 : 09:48:33

Thanks for the reply. I am using this query:

SELECT e.ExamID,e.Title,e.Description,e.Duration,e.TotalQuestions,
eq.QuestionText, ec.ChoiceText,eq.ExamQuestionID
FROM test_Exams e
JOIN test_ExamQuestions eq ON e.ExamID = eq.ExamID
JOIN test_ExamChoices ec ON eq.ExamQuestionID = ec.ExamQuestionID
WHERE e.ExamID = 1
AND e.ExamStatusID = 1

Mohammad Azam
www.azamsharp.net

mr_mist
Grunnio

1870 Posts

Posted - 2006-03-16 : 10:11:42

Ok well it looks like you are getting what you are asking for. I'm not entirely sure what result you intend to see.

-------
Moo. :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-16 : 12:37:49

It is your job on the webpage to figure out how to format the results. YOu probably need to keep track of the current question number, and if it is a new one, print the question "header". Then with subsequent choices, you just print the choices. When you get a new question, you output a blank line or something and then create the next question "header". You just need to write a little bit of code at your presentaion layer to handle this.

Also, it is very important to be sure that you SORT the output in your SELECT otherwise the choices for a question will not necessarily line up.

Are you using ASP or ASP.NET or something else?

The important thing to realize is -- you do NOT need T-SQL help, as the subject of your initial post states (other than adding an ORDER BY clause), but rather you might need web-programming help. The database is returning the data just fine, you need to use your client to present it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources