How to modify this SQL join to include all columns?

Question

I have these three tables for a little quiz. Each question has one correct and three wrong answers

Table Name:            Columns:
Questions                   QuestionID, QuestionText, 
                            AnswerID (this stores id of correct answer)

Answers                     AnswerID, AnswerText, QuestionID
QuestionsAnswers            QuestionID,AnswerID

This query

SELECT QuestionText, AnswerText
FROM [Questions] LEFT OUTER JOIN [Answers]
ON Questions.QuestionID=Answers.AnswerID;

gives me the following result

What is the capital of England? London
What is the capital of France?  Paris
What is the capital of USA?         Washington

I actually need to see the other three wrong answers grouped with each question as well. Kind of like

 What is the capital of England?    London
 What is the capital of England?    Berlin
 What is the capital of England?    BikiniBottom
 What is the capital of England?    Nottingham
 ... -- more results for France, USA and others follow

How can I modify my query above to get a similar result? Thanks

zxc · Accepted Answer · 2013-08-07 08:50:14Z

3

SELECT QuestionText, AnswerText FROM [Answers] LEFT OUTER JOIN [Question] ON Answers.QuestionID=Questions.QuestionID;

answered Aug 7, 2013 at 8:50

zxc

1,5263 gold badges13 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adriaan Stander · Accepted Answer · 2013-08-07 08:49:25Z

2

From your table structure above, how about

SELECT QuestionText, AnswerText
FROM [Questions] LEFT OUTER JOIN [Answers]
ON Questions.QuestionID=Answers.QuestionID;

Note Answers.QuestionID not Answers.AnswerID

answered Aug 7, 2013 at 8:49

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

1 Comment

iAteABug_And_iLiked_it Over a year ago

Thank you. All answers were good and worked, but yours came the fastest and also the last line with the note was important.Merci

bvr · Accepted Answer · 2013-08-07 08:49:47Z

2

Try this

SELECT QuestionText, AnswerText
FROM [Questions] LEFT OUTER JOIN [Answers]
ON Questions.QuestionID=Answers.QuestionID;

answered Aug 7, 2013 at 8:49

bvr

4,8361 gold badge23 silver badges24 bronze badges

Comments

Robert · Accepted Answer · 2013-08-07 08:51:26Z

1

Maybe you have to join with QuestionsAnswers Try below solution

SELECT QuestionText, AnswerText
FROM [Questions] 
JOIN [QuestionsAnswers] ON QuestionsAnswers.QuestionID=QuestionsAnswers.AnswerID;
JOIN [Answers] ON QuestionsAnswers.AnswerID=Answers.AnswerID;

answered Aug 7, 2013 at 8:51

Robert

25.8k8 gold badges70 silver badges86 bronze badges

Collectives™ on Stack Overflow

How to modify this SQL join to include all columns?

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related