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.
| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2002-08-29 : 18:18:46
|
Ok, I currently have this implemented in a really, really poor manner (recursive queries from ASP). I'm sure there's a better way, but damned if I can figure it out.My situation is this: a tables of questions, a table of potential answers to those questions, and a join table of users' actual answers to the questions. Many questions allow multiple answers to be selected.Here's a quick approximation of the schema:create table users (i int identity, user_name varchar(50))create table surveys (i int identity, survey_name varchar(50))create table questions (i int identity, i_surveys int, question varchar(50))create table answers (i int identity, answer varchar(50))create table user_answers (i_users int, i_questions int, i_answers int) What I'm looking for is to get a count of how many questions out of how many possible questions a user has answered for a survey. IE, if a user had answered 15 questions on survey #3, and survey #3 has 25 total questions:Surveys.i Answered Total Questions--------- -------- --------------- 3 15 25 Of course, that user may have picked 50 distinct answers, but many of them were multiple selections for a single question. It's the number of questions answered here that I'm after.It doesn't *seem* that tough, conceptually, but I just can't seem to get it right.Thanks in advance-b |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-08-29 : 18:44:18
|
| Nevermind. Got it. Duh.Thanks-b |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-08-29 : 19:05:21
|
| Damn, you sniped me. I was working on the solution.Can you post what you came up with?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-08-29 : 19:56:16
|
Well, I have to admit that I cheated a bit. I had the whole thing except the "count total possible questions per survey" when I realized that questions are infrequently added, so it was probably more efficient to just add a "questions" int field to the surveys table, and have that field hold the number of questions in any given survey. Then a trigger on the questions table updates that number whenever a question is added/deleted.Given that change, here's what I went with:select surveys.i,surveys.name,count(distinct user_answers.i_questions) as answered,surveys.questions from surveys left join questions on questions.i_surveys=surveys.i left join user_answers on user_answers.i_questions=questions.i and user_answers.i_users=@iUsers group by surveys.i,surveys.name,surveys.questions Cheers -- thanks for taking a look at it.-b |
|
|
|
|
|
|
|
|
Complex query: help!