Maël Wyssbrod Maël Wyssbrod - 2 years ago 70
SQL Question

(SQL) Any Ideas for a Query?


So I have a Database with a few tables in it and should join them in a way I can't imagine right now. My SQL Skills are very basic so that's not helping me either.

But here is the current situation:

I have a table Questions and a table Answers

Each Question has a ID and a Text and 4 answers, which are located in the Answers table. In the Answers-column "Correct" there is either true or false(Is it a false or a true answer)


Now, the tricky part is, that I have 2 more tables, named Teams and TAnswers. If a User answers a question, his choice goes into the TAnswers-table.

enter image description here

So a

gives me this:

enter image description here

And finally, to generate a Result-table, I want it to look like that:


Can anybody help me? Sorry for my eventually bad writing, i've had a little bit too much coffee :)


So after some trying around and reading through a Reddit tutorial(Kudos to Rich Brenner) I found the answer myself(The other ones were good but not what I searched for). So here is my answer:

SELECT Team, COUNT(a.TeamID) as [Correct Answers] FROM TAnswers as a
RIGHT JOIN Answers ON a.AnswerID = Answers.ID
RIGHT JOIN Teams ON a.TeamID = Teams.IDTeam
WHERE Correct = 1
ORDER BY [Correct Answers] desc;

Answer Source

Ok, I'll help you out here. This seems like a homework question so I'm not going to give you the answer, rather I'm going to help you get there yourself.

Your base table is going to be 'TAnswers' as this has the keys to join to the other tables. You're then looking to complete left joins to the other tables.

FROM TAnswers a
LEFT JOIN SecondTable b on a.Field = b.Field
LEFT JOIN ThirdTable c on a.Field = c.Field
LEFT JOIN FourthTable d on a.Field = d.Field

You'll have to work what fields you're going to use for the joins.

You'll then need to work out how you're going to count your correct answers, are you going to use a SUM or a WHERE clause? Up to you.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download