Derived Column Subquery with nText

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)

Derived Column Subquery with nText

Author

Topic

jfslater98
Starting Member

7 Posts

Posted - 2006-05-26 : 10:20:15

Hi Everyone,

Got kind of a strange one here and it's a 2-parter. We have a testing system that tracks users' answers to questions, both correct and incorrect. Management wants a report that shows incorrect answers people chose, plus the answer they should've chosen right next to it. I have posted the DDL at the end of this message.

1) So this query works on my sample data:

SELECT uprofile.fname as [First Name], uprofile.lname as [Last Name], question.qbody as [Question],

answer.abody as [Incorrect Answer], q.correct,
(select a.aid from answer a where a.qid=q.qid and a.correct = 1) as
[Correct Answer]
FROM uprofile INNER JOIN
uquestion q ON uprofile.uid = q.uid INNER JOIN
question ON q.qid = question.qid INNER JOIN
answer ON q.aid = answer.aid AND question.qid = answer.qid
WHERE (q.correct = N'0')
ORDER BY uprofile.lname

but if I swap the 'a.aid' element with 'a.body' (which is what management wants to see), I get this error:

Server: Msg 279, Level 16, State 3, Line 1
The text, ntext, and image data types are invalid in this subquery or aggregate expression.

How do I get that answer body in that column?

2) This is an edit: I found that one question is a multiple/multiple choice, so there is multiple correct answers, and the query above gaks unless I filter it out. I am not sure how to handle the multiples, does anyone have an idea on that? Thanks.

John

--DDL Follows

CREATE TABLE [dbo].[answer] (
[aid] [float] NOT NULL ,
[qid] [float] NOT NULL ,
[abody] [ntext] NULL ,
[correct] [nvarchar] (255) NULL ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[question] (
[qid] [float] NOT NULL ,
[qbody] [ntext] NULL ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[uprofile] (
[uid] [float] NOT NULL ,
[fname] [nvarchar] (255) NULL ,
[lname] [nvarchar] (255) NULL ,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[uquestion] (
[uid] [float] NOT NULL ,
[qid] [float] NOT NULL ,
[aid] [float] NOT NULL ,
[correct] [nvarchar] (255) NOT NULL
) ON [PRIMARY]
GO

INSERT INTO answer(aid, qid, abody, correct) values(1,1, 'A stain',0)
INSERT INTO answer(aid, qid, abody, correct) values(2,1, 'A globule',1)
INSERT INTO answer(aid, qid, abody, correct) values(3,1, 'A trowel',0)
INSERT INTO answer(aid, qid, abody, correct) values(4,1, 'Justin Guarini',0)

INSERT INTO answer(aid, qid, abody, correct) values(5,2, 'Nuclear Scientist',0)
INSERT INTO answer(aid, qid, abody, correct) values(6,2, 'Haberdasher',1)
INSERT INTO answer(aid, qid, abody, correct) values(7,2, 'Chimney Sweep',0)
INSERT INTO answer(aid, qid, abody, correct) values(8,2, 'Katharine McPhee',0)

INSERT INTO answer(aid, qid, abody, correct) values(9,3, 'Yes',0)
INSERT INTO answer(aid, qid, abody, correct) values(10,3, 'No',0)
INSERT INTO answer(aid, qid, abody, correct) values(11,3, 'NO, were not doing Stonehenge!',1)
INSERT INTO answer(aid, qid, abody, correct) values(12,3, 'Clay Aiken',0)

INSERT INTO question(qid, qbody) values(1, 'What did Scotland Yard find?')
INSERT INTO question(qid, qbody) values(2, 'What would Nigel become if not a bassist?')
INSERT INTO question(qid, qbody) values(3, 'Are we going do Stonehenge tomorrow')

INSERT INTO uprofile(uid, fname, lname) values(23, 'Derek', 'Smalls')
INSERT INTO uprofile(uid, fname, lname) values(24, 'Marty', 'DiBergi')
INSERT INTO uprofile(uid, fname, lname) values(25, 'Bobbi', 'Fleckman')

INSERT INTO uquestion(uid, qid, aid, correct) values(23, 1, 3, 0)
INSERT INTO uquestion(uid, qid, aid, correct) values(23, 2, 8, 0)
INSERT INTO uquestion(uid, qid, aid, correct) values(24, 1, 4, 0)
INSERT INTO uquestion(uid, qid, aid, correct) values(24, 2, 7, 0)
INSERT INTO uquestion(uid, qid, aid, correct) values(25, 1, 2, 1)
INSERT INTO uquestion(uid, qid, aid, correct) values(25, 2, 6, 1)
INSERT INTO uquestion(uid, qid, aid, correct) values(23, 3, 9, 0)
INSERT INTO uquestion(uid, qid, aid, correct) values(24, 3, 10, 0)
INSERT INTO uquestion(uid, qid, aid, correct) values(25, 3, 11, 1)

drop table answer
drop table question
drop table uprofile
drop table uquestion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources