Two Tables into one result 1

[jrball]

I have two tables I am trying to get into one query, but I can't figure out how to do it.

What I need is a final result with all 4 of the answer fields on the same line as the each one of the question fields.

Table 1 - Answers

Answer_ID Question_ID Answer_Text Correct
1 1 Answer1 True
2 1 Answer2 False
3 1 Answer3 False
4 1 Answer4 False
5 2 Answer1 True
6 2 Answer2 False
7 2 Answer3 False
8 2 Answer4 False

Table 2 - Questions

Question_ID Course_ID Question_Text Question_Num
1 FUN001 What is #1? 1
2 FUN001 What is #2? 2

 

[Rofeu]

Hi,

you'll need to link the answer table to the question table 4 times.

Something along the lines of:

SELECT Q.Q_ID, A1.Answer_Text, A2.Answer_Text, A3.Answer_Text, A4.Answer_Text
FROM Questions Q, Answers A1, Answers A2, Answers A3, Answers A4
WHERE Q.QuestionID = A1.QuestionID
AND Q.QuestionID = A2.QuestionID
AND Q.QuestionID = A3.QuestionID
AND Q.QuestionID = A4.QuestionID;

Mind you, this is quasi-pseudo-code and fully untested.

Cheers,

roel

 

[jrball]

That was exactly what I was looking for. I was trying JOINS, but I don't think I was setting them up right.

 

[gmmastros]

Here's one example of how this can be accomplished.

The problem with this approach is that it assumes that there will always be 4 answers per question and that the answerid's will always be consecutive. If this is a bad assumption, then you could always dump the data in to a table variable with an identity column to get your final result set.

Notice that I put your test data in to table variables. This means you can copy/paste the code in to a query analyzer window and run it. If this appears to suit your needs, then you will need to modify this to use your real tables instead.

[COLOR=blue]Declare[/color] @Answer [COLOR=blue]Table[/color](AnswerId [COLOR=blue]Int[/color], QuestionId [COLOR=blue]int[/color], Answer_Text [COLOR=blue]VarChar[/color](100), Correct [COLOR=blue]Bit[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Answer [COLOR=blue]values[/color](1,1,[COLOR=red]'Answer1'[/color],1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Answer [COLOR=blue]values[/color](2,1,[COLOR=red]'Answer2'[/color],0)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Answer [COLOR=blue]values[/color](3,1,[COLOR=red]'Answer3'[/color],0)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Answer [COLOR=blue]values[/color](4,1,[COLOR=red]'Answer4'[/color],0)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Answer [COLOR=blue]values[/color](5,2,[COLOR=red]'Answer1'[/color],1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Answer [COLOR=blue]values[/color](6,2,[COLOR=red]'Answer2'[/color],0)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Answer [COLOR=blue]values[/color](7,2,[COLOR=red]'Answer3'[/color],0)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Answer [COLOR=blue]values[/color](8,2,[COLOR=red]'Answer4'[/color],0)


[COLOR=blue]Declare[/color] @Questions [COLOR=blue]Table[/color](Question_ID [COLOR=blue]Int[/color], Course_ID [COLOR=blue]VarChar[/color](20), Question_Text [COLOR=blue]VarChar[/color](100), Question_Num [COLOR=blue]Int[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Questions [COLOR=blue]Values[/color](1,[COLOR=red]'FUN001'[/color],[COLOR=red]'What is #1?'[/color],1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Questions [COLOR=blue]Values[/color](2,[COLOR=red]'FUN001'[/color],[COLOR=red]'What is #2?'[/color],2)

[COLOR=blue]Select[/color] 	Q.*, 
		[COLOR=#FF00FF]Min[/color]([COLOR=blue]Case[/color] [COLOR=blue]When[/color] AnswerId % 4  = 0 [COLOR=blue]Then[/color] AnswerId [COLOR=blue]End[/color]),
		[COLOR=#FF00FF]Min[/color]([COLOR=blue]Case[/color] [COLOR=blue]When[/color] AnswerId % 4  = 0 [COLOR=blue]Then[/color] Answer_Text [COLOR=blue]End[/color]),
		[COLOR=#FF00FF]Min[/color]([COLOR=blue]Case[/color] [COLOR=blue]When[/color] AnswerId % 4  = 0 [COLOR=blue]Then[/color] [COLOR=#FF00FF]Convert[/color]([COLOR=blue]int[/color], Correct) [COLOR=blue]End[/color]),
		[COLOR=#FF00FF]Min[/color]([COLOR=blue]Case[/color] [COLOR=blue]When[/color] AnswerId % 4  = 1 [COLOR=blue]Then[/color] AnswerId [COLOR=blue]End[/color]),
		[COLOR=#FF00FF]Min[/color]([COLOR=blue]Case[/color] [COLOR=blue]When[/color] AnswerId % 4  = 1 [COLOR=blue]Then[/color] Answer_Text [COLOR=blue]End[/color]),
		[COLOR=#FF00FF]Min[/color]([COLOR=blue]Case[/color] [COLOR=blue]When[/color] AnswerId % 4  = 1 [COLOR=blue]Then[/color] [COLOR=#FF00FF]Convert[/color]([COLOR=blue]int[/color], Correct) [COLOR=blue]End[/color]),
		[COLOR=#FF00FF]Min[/color]([COLOR=blue]Case[/color] [COLOR=blue]When[/color] AnswerId % 4  = 2 [COLOR=blue]Then[/color] AnswerId [COLOR=blue]End[/color]),
		[COLOR=#FF00FF]Min[/color]([COLOR=blue]Case[/color] [COLOR=blue]When[/color] AnswerId % 4  = 2 [COLOR=blue]Then[/color] Answer_Text [COLOR=blue]End[/color]),
		[COLOR=#FF00FF]Min[/color]([COLOR=blue]Case[/color] [COLOR=blue]When[/color] AnswerId % 4  = 2 [COLOR=blue]Then[/color] [COLOR=#FF00FF]Convert[/color]([COLOR=blue]int[/color], Correct) [COLOR=blue]End[/color]),
		[COLOR=#FF00FF]Min[/color]([COLOR=blue]Case[/color] [COLOR=blue]When[/color] AnswerId % 4  = 3 [COLOR=blue]Then[/color] AnswerId [COLOR=blue]End[/color]),
		[COLOR=#FF00FF]Min[/color]([COLOR=blue]Case[/color] [COLOR=blue]When[/color] AnswerId % 4  = 3 [COLOR=blue]Then[/color] Answer_Text [COLOR=blue]End[/color]),
		[COLOR=#FF00FF]Min[/color]([COLOR=blue]Case[/color] [COLOR=blue]When[/color] AnswerId % 4  = 3 [COLOR=blue]Then[/color] [COLOR=#FF00FF]Convert[/color]([COLOR=blue]int[/color], Correct) [COLOR=blue]End[/color])
[COLOR=blue]From[/color] 	@Questions Q
        [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] @Answer A
          [COLOR=blue]On[/color] Q.Question_ID = A.QuestionId
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] Q.Question_ID, Q.Course_ID, Q.Question_Text, Q.Question_NUM

-George

"the screen with the little boxes in the window." - Moron

 

[AlexCuse]

If your answerID's are sequential, as in the example, you could do something like this:

select a.*
,max(case when b.Answer_ID % 4 = 1 then a.Answer_Text else '' end) as Question_1
,max(case when b.Answer_ID % 4 = 1 then a.Correct else '' end) as Correct_1
---repeat for other answers, correct/not 'columns'
from Questions a
inner join Answers b
on a.Question_ID = b.Question_ID

I suggest you read in BOL about JOIN, CASE, MAX functions.

The modulus operator ('%') gives you the remainder.

For example, if Answer_ID = 5, then Answer_ID % 4 = 1.

IF they are sequential, this is an easy way to tell question1 from question2, etc...

Answer_ID % 4 value Answer #
1 1
2 2
3 3
0 4


Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Of course

as Question_1

should read

as Answer_1



Off to get some tea...

Ignorance of certain subjects is a great part of wisdom

 

[ESquared]

Questions to think about:

What if you do this again and you have a different number of answers? You'll have to rewrite your queries perhaps. Best design it in a way to be tolerant of that, if there's any chance of reuse or future modification.

Is AnswerText the only way to tell apart answers from each other? Can you add another column with RespondentID or something? The design strikes me as lacking something.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]