Maël Wyssbrod Maël Wyssbrod - 7 months ago 9
SQL Question

(SQL) Any Ideas for a Query?

Community,

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)

Tables

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

SELECT * FROM TAnswers
gives me this:

enter image description here

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

TEAM | CORRECT ANSWERS


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

EDIT

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
GROUP BY Team
ORDER BY [Correct Answers] desc;

Answer

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.

SELECT
a.Team
,d.Field
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.