SQL Query problem: How to merge two lists

Question

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)

Solution

From what I understand, you have a single list of answers, but the question could be in any of a couple of different tables. There are two approaches you could take to get this one - either a couple of LEFT JOINs with ISNULL, or you could create a VIEW that's a UNION of your question tables and JOIN to that.

If it's just this one query and you won't be adding more question types in the future, the first is probably easier, but if you're going to be doing this often (adding question types or querying the data), I'd go with method 2 since you'll only have to maintain the view and 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 be pulled regardless of which table it appears in.

METHOD 2:

First, create a view with a UNION of all your questions, like this (add as many more unions as you want):

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