65857

SQL Query problem: How to merge two lists

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)

Answer1:

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.

<strong>METHOD 1:</strong>

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.

<strong>METHOD 2:</strong>

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

Recommend

  • gcc 4.7.2 in Debian wheezy doesn't always properly align stack pointer. Is this a bug?
  • Macro perform integer arithmetic
  • Build a table which has a Field of User_ids, and then querying for a particular User_Id
  • Exchange web service set organizer of the appointment
  • Unable to issue identity card using Hyperledger Composer Javascript API
  • Error in Compiling haskell .ll file with llvm backend
  • LISP - How to get the average length for nested lists?
  • Android multuple camera preview
  • HTTPS + gzip: Is it a security vulnerability if I only gzip non-sensitive files?
  • How to produce a Code 39 that can be reliably read after faxing
  • How to insert multiple arrays into a database?
  • string Multiplication in C++
  • Combine two small queries (that group by different values) into one query
  • Using Select and where statement in Criteria
  • Direct insert data in mysql data in android
  • Getting data from database using HQL
  • Pandas: merge_asof() sum multiple rows / don't duplicate
  • Submission of new app with iAds
  • How to merge two Request in Laravel
  • How to change default stop edit behavior in jtable
  • Specify HTTPS for custom WCF Binding
  • Making Google Visualization - Annotation Chart to work in GWT
  • How to specify input and output paths from cmd.exe for a PowerShell script?
  • Implicit joins and Where in Doctrine - how?
  • I18n locale disregarding fallbacks
  • converting text file into xml using php?
  • Sequential (transactional) API calls in angular 4 with state management
  • How can I set a binding to a Combox in a UserControl?
  • How to write order and limit within cakephp joins array
  • Javascript Callbacks with Object constructor
  • Jquery - Jquery Wysiwyg return html as a string
  • Arrays break string types in Julia
  • Matrix multiplication with MKL
  • WPF Applying a trigger on binding failure
  • How to set the response of a form post action to a iframe source?
  • Java static initializers and reflection
  • Change div Background jquery
  • Qt: Run a script BEFORE make
  • reshape alternating columns in less time and using less memory