The Old Joel on Software Forum: Part 3 (of 5)

SQL question

I have a table that stores answers to survey questions. Each survey has maybe 10 questions, with answers being "Totally Disagree, Somewhat Disagree, Somewhat Agree, Totally Agree".

I'd like to build a report that assigns values to each answer, on a scale of 1 - 4. I created a lookup table, with the four possible answers and their respective values. Now I'm stuck while trying to build the query. I would have thought I could just do a Join, but each row has essentially 10 lookups. Am I missing something?

Thanks.

nathan
Tuesday, December 9, 2003

Provide the table schema (create table statements).

MR
Tuesday, December 9, 2003

Original table:
CREATE TABLE [tableName] (
    [tp_ID] [int] IDENTITY (1, 1) NOT NULL ,
    [tp_Title] [nvarchar] (255) NULL ,
    [tp_Modified] [datetime] NULL ,
    [tp_Created] [datetime] NULL ,
    [tp_Author] [int] NULL ,
    [tp_Editor] [int] NULL ,
    [tp_Version] [int] NULL ,
    [tp_Name] [nvarchar] (255) NULL ,
    [tp_DOB] [datetime] NULL ,
    [RecordNumber] [float] NULL ,
    [tp_Survey_x0020_Date] [datetime] NULL ,
    [Question1] [nvarchar] (255) NULL ,
    [Question2] [nvarchar] (255) NULL ,
    [Question3] [nvarchar] (255) NULL ,
    [Question4] [nvarchar] (255) NULL ,
    [Question5] [nvarchar] (255) NULL ,
    [Question6] [nvarchar] (255) NULL ,
    [Question7] [nvarchar] (255) NULL ,
    [Question8] [nvarchar] (255) NULL ,
    [Question9] [nvarchar] (255) NULL ,
    [tp_Comments_x003a_] [ntext] NULL ,
    [tp_Entity] [int] NULL ,
    [tp_Program] [int] NULL
) ON [PRIMARY]

Lookup table:
CREATE TABLE [ResponseLookup] (
    [response] [nvarchar] (255) NOT NULL ,
    [score] [int] NOT NULL
) ON [PRIMARY]

nathan
Tuesday, December 9, 2003

All the Question columns need to be replaced with the value in the lookup table.

nathan
Tuesday, December 9, 2003

1 Test can have many different questions.
1 Question can have many different answers.
1 Answer can be used for many different Questions.

Create 4 tables

tblTest
tblQuestions
tblQAIntersect
tblAnswers

Make sure to have a primary key in each and a foreign key in the ones that need to be joined.

tblQA Intersect would have 2 fields as the primary key, the tblQuestions PK and the tblAnswers PK.

This may help make the query easier to write. Unless I don't understand the problem fully.

Dave B.
Tuesday, December 9, 2003

That's not a very good table design -- this is a classic problem that arises when you don't bother with any normalization.

You should have something like (I don't care what the table/column names are):
Survey( SurveyID, date, user, etc. )
Question( QuestionID, Text, Weight, etc. )
Question_Answer( SurveyID, QuestionID, Answer )

Then you can easily join to get the answers and the weight.

With your current system you'd have to do 10 joins, or ignore the lookup table and hard-code the weight in a CASE statement. Good luck.

MR
Tuesday, December 9, 2003

>With your current system you'd have to do 10 joins, or ignore the lookup table and hard-code the weight in a CASE statement. Good luck.

That's what I'm stuck with. I didn't create the original table, and I can't change it ever. It's created by an [expletive deleted] application we use. I did create the lookup table as a first attempt to get the data in some kind of usable format.

Is there another lookup table format that would be more helpful?

nathan
Tuesday, December 9, 2003

Well like I said, you can do 10 joins:
select testID,
t1.weight,
t2.weight,
...,
t10.weight
from test
inner join weight t1 on test.question1 = t1.question
inner join weight t2 on ...
....

Or maybe you could do something like:
select testID,
case question1
when 'Foo'
then 1
when 'bar'
then 2
else
1213
end,
case question2
etc.

There might be some other SQL tricks that you can do, but I don't think that you can do it easily.

MR
Tuesday, December 9, 2003

You may still be able to create your own tables and "Select" the data from the original into your own or possible write a VB program to do it.

Dave B.
Tuesday, December 9, 2003

>With your current system you'd have to do 10 joins,

Ok. Here's what I came up with. If anyone knows a better way without changing changing existing tables (adding tables is fine), please let me know.

SELECT [tp_Survey_x0020_Date],
    R.score as [Question1],
    E.score as [Question2],
    S.score as [Question3],
    P.score as [Question4],
    O.score as [Question5],
    N.score as [Question6],
    U.score as [Question7],
    L.score as [Question8],
    K.score as [Question9]
FROM SurveyTable
JOIN ResponseLookup R ON [Question1] = R.response
JOIN ResponseLookup E ON [Question2] = E.response
JOIN ResponseLookup S ON [Question3] = S.response
JOIN ResponseLookup P ON [Question4] = P.response
JOIN ResponseLookup O ON [Question5] = O.response
JOIN ResponseLookup N ON [Question6] = N.response
JOIN ResponseLookup U ON [Question7] = U.response
JOIN ResponseLookup L ON [Question8] = L.response
JOIN ResponseLookup K ON [Question9] = K.response

nathan
Tuesday, December 9, 2003

Maybe you could design some views that make the data a little more normalized, and then join the views together? It's clearly not going to be performant no matter what you do, but you may at least be able to simplify the select SQL.

Brad Wilson (dotnetguy.techieswithcats.com)
Tuesday, December 9, 2003

Thanks guys. looks like the 10 joins is the quickest, though definitely one of the dirtiest.

nathan
Tuesday, December 9, 2003

You could add a table which simulates the normalized schema I had before:

e.g.
create table question_answer(
testID,
question,
answer
)

-- this is all T-SQL pseudocode
create trigger survey_update
on
survey
for update

if update( question1 )
-- change the question_answer table
...
end

create trigger survey_insert
on survey
for insert
insert into question_answer( inserted.quizID, 1, inserted.Question1 )
insert into question_answer( inserted.QuizID, 2, inserted.Question2 )
...
end

create trigger survey_delete
on survey
for delete

delete from question_answer where quiz_id = deleted.quizID

end

Then you could do the join to question_answer.

MR
Tuesday, December 9, 2003

Might I ask what the [expletive deleted] application is, so the rest of us may avoid such misfortune in the future?

Greg Hurlman (blogs.squaretwo.net)
Tuesday, December 9, 2003

CommandBridge from Mariner. Apparently it's built on top of SharePoint Portal Services. The IT dept made the decision to use this before I was hired. I'd rather just use SPS rather than using this product that sits on top. Overall the software is ok, just in this one "self-service" component things get messy. They allow end-users to create surveys & forms on the fly. Their method of storing the data in these ad hoc forms is through the table structure I showed earlier.

nathan
Tuesday, December 9, 2003

It seems like it is just using the normal SharePoint Team services list/survey functionality, so not only are you using a crappy application, but your company actually paid extra for it to make STS (which is included in Office XP for free) even worse than it already was? (or did they pay extra to make the full Sharepoint Portal Server worse?)

ChrisO
Wednesday, December 10, 2003

I think we paid more to get STS. We also got MapPoint, and supposedly we can do some cool stuff with what we have, just nobody knows how to do any of it, let alone explain it to the end users. It's a mess.

nathan
Wednesday, December 10, 2003