SQL Query problem: How to merge two lists.
- by ProfK
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?
