CODEWITHSUNDEEP
sqldatabasejoin
iAteABug_And_iLiked_it
iAteABug_And_iLiked_it

Reputation: 3795

How to modify this SQL join to include all columns?

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

Upvotes: 1

Views: 105

Answers (4)

Robert
Robert

Reputation: 25753

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;

Upvotes: 1

zxc
zxc

Reputation: 1526

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

Upvotes: 3

bvr
bvr

Reputation: 4826

Try this

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

Upvotes: 2

Adriaan Stander
Adriaan Stander

Reputation: 166416

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

Upvotes: 2

Related Questions