I have a base table of Participants and Answers, with the following structure:
ParticipantId, BusUnitId, QuestionNum, Answer.
In this table, QuestionNum ranges, say, from 1 to 6. I also have two other tables that sort of link QuestionNum to the actual question table, BusUnitQuestions, and ParticipantQuestions. For each QuestionNum, I must get the actual question text, based on QuestionId.
BusUnitId, QuestionId
ParticipantId, QuestionId
Now assuming records with QuestionNum from 1 to 6. BusUnitQuestions has 3 records, so QuestionNum 1 to 3 must join to Question on the QuestionId's from BusUnitQuestions, and QuestionNum 4 to 6 must join to Question on the QuestionId's from ParticipantQuestions. I assume I need to use ROW_NUMBER() in the subquery from BusUnitQuestions to join to my answer table, but I'm lost after that.
If anyone at all understands me, do you have any suggestions?
Below is a sample setup. In this, the particpant has answered 5 questions (1 to 5). The first three of these questions are set by the participant's department, and the last two chosen by the participant. There are in reality a lot more than 5 questions for the department and participant to choose from. I need to join the Questions table to the Answers table, using the row numbers in DepartmentQuestions and ParticipantQuestions corresponding to QuestionNum in the Answers table.
create table Answers (AnswerId int identity(1,1), ParticipantId int, DeptId int, QuestionNum int, AnswerScore int)
create table Dept_Question (DqId int identity(1,1), DeptId int, QuestionId int)
create table Particpant_Question (PqId int identity(1,1), ParticipantId int, QuestionId int)
create table Questions (QuestionId int identity(1,1), QuestionText nvarchar(200))
insert Questions (QuestionText) values ('What is a duck?')
insert Questions (QuestionText) values ('How much do you weigh?')
insert Questions (QuestionText) values ('Why does orange fit?')
insert Questions (QuestionText) values ('Who pokes the fish?')
insert Questions (QuestionText) values ('Why no cow bells?')
insert Dept_Question (DeptId, QuestionId) values (3, 3)
insert Dept_Question (DeptId, QuestionId) values (3, 4)
insert Dept_Question (DeptId, QuestionId) values (3, 1)
insert Particpant_Question(ParticipantId, QuestionId) values (1, 2)
insert Particpant_Question(ParticipantId, QuestionId) values (1, 4)
insert Answers (ParticipantId, DeptId, QuestionNum, AnswerScore) values (1, 3, 1, 63)
insert Answers (ParticipantId, DeptId, QuestionNum, AnswerScore) values (1, 3, 2, 89)
insert Answers (ParticipantId, DeptId, QuestionNum, AnswerScore) values (1, 3, 3, 44)
insert Answers (ParticipantId, DeptId, QuestionNum, AnswerScore) values (1, 3, 4, 54)
insert Answers (ParticipantId, DeptId, QuestionNum, AnswerScore) values (1, 3, 5, 72)