SQL Query problem: how to merge two lists
I have a basic Participants and Responses table with the following structure:
ParticipantId, BusUnitId, QuestionNum, Answer.
In this table, QuestionNum ranges from, say, 1 to 6. I also have two other tables that refer to the QuestionNum reference to the actual question table, BusUnitQuestions and ParticipantQuestions. For each QuestionNum, I have to get the actual question text based on the QuestionId.
BusUnitId, QuestionId
ParticipantId, QuestionId
Now we assume records with QuestionNum 1 to 6. BusUnitQuestions has 3 records, so QuestionNum 1 to 3 should join QuestionId from BusUnitQuestions and QuestionNum 4-6 should join QuestionId from ParticipantsQuestions. I guess I need to use ROW_NUMBER () on a subquery from BusUnitQuestions to join my response table, but after that I got lost.
If anyone understands me, do you have any suggestions?
Below is an example setting. In this case, the participant answered 5 questions (from 1 to 5). The first three of these questions are asked by the contributor department, and the last two are selected by the contributor. In fact, there are more than 5 questions for the department and the participant to choose from. I need to join the Questions table in the Answers table using the line numbers in the Departments and Members sections corresponding to the Question 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)
a source to share
From what I understand you have one list of answers, but the question could be in any of several different tables. There are two approaches you could take to get this, either a pair of LEFT JOINs with ISNULL, or you can create a VIEW which is a UNION of your question tables and a JOIN to do so.
If it's only one query and you won't be adding more question types in the future, the former will probably be easier, but if you do it often (adding question types or querying data) I would go with method 2 as you need will support the view, not your original queries.
METHOD 1:
SELECT a.ParticipantId, a.QuestionId, a.AnswerId, ISNULL(q1.QuestionText, q2.QuestionText, NULL) as QuestionText
FROM Answers a
LEFT
JOIN Question1 q1 /* BusinessUnitQuestions? */
ON a.QuestionId = q1.QuestionId
LEFT
JOIN Question2 q2 /* ParticipantQuestions? */
ON a.QuestionId = q2.QuestionId
This way the question text will stretch no matter what table it is in.
METHOD 2:
First create a UNION view of all your questions, for example (add as many unions as you like):
CREATE VIEW AllQuestions
AS
SELECT QuestionId, QuestionText
FROM Question1
UNION ALL
SELECT QuestionId, QuestionText
FROM Question2
Then you can use the view in a simplified version of the first query:
SELECT a.ParticipantId, a.QuestionId, a.AnswerId, q.QuestionText
FROM Answers a
JOIN AllQuestions q
ON a.QuestionId = q.QuestionId
a source to share