JOIN Query issue

[Yuki]

Hello, I can't seem to get my head round querying what I want.

 

 

Below is my current query (where it's at) and also a script to create/populate the database.

 

I want the following results from the query:

 

question_Desc, count(choice.choice_ID), Correct Answer

 

So basically it would be similar to what the current script is returning, but with an additional column counting the choices.

 

I.e.

 

Question                          Choices            Answer

Apple is what colour?          4                      Red

Orange is what colour?        3                      Orange

 

 

SELECT question.question_Desc 'question',count(choice.choice_No) 'choices'
FROM question
LEFT JOIN choice
USING (question_ID)
GROUP BY question.question_ID;

 

 

[Barand]

Close, just the quotes are wrong

SELECT question.question_Desc as question , COUNT(choice.choice_No)  as choices,
    question.question_Correct_Answer as answer
FROM question
LEFT JOIN choice
USING (question_ID)
GROUP BY question.question_ID;

[Yuki]

Ah that's no the result I'm going for though, I want the choice_Desc using question.question_Correct_Answer, so instead of an ID I want "Red" and "Orange"

[Barand]

That's not going to happen.

 

PK of choices is 2 int fields.

 

question_correct_answer is varchar(10)

 

You need to make it INT with same value as the choice_no

[Yuki]

 

 

Okay I've changed question.question_Correct_Answer to be the same type as choice.choice_No

 

 

 

I have my desired result working as two seperate queries.

 

SELECT question.question_Desc 'question',choice.choice_No 'choice', choice.choice_Desc 'option'
FROM question
LEFT JOIN choice
ON question.question_ID = choice.question_ID
WHERE question.question_Correct_Answer = choice.choice_No;

 

returns

 

 

question  choice  option 

Apple is what colour? 1 Red

Orange is what colour? 3 Orange

 

 

SELECT question.question_Desc 'question',count(choice.choice_No) 'choices'
FROM question
LEFT JOIN choice
USING (question_ID)
GROUP BY question.question_ID;

 

 

returns

 

question  choices 

Apple is what colour? 4

Orange is what colour? 3

 

 

 

Thanks for your help Barand I hope you can help me come to a conclusion.

 

 

[Barand]

try (not tested)

SELECT question.question_Desc as question , COUNT(choice.choice_No)  as choices,
    c2.choice_Desc as answer
FROM question
LEFT JOIN choice USING (question_ID)
LEFT JOIN choice c2 ON question.question_ID = c2.question_ID AND question.question_correct_answer = c2.choice_No
GROUP BY question.question_ID;

[Yuki]

That works, thanks very much, I was up for hours trying to get it working as a subquery.

 

If you have some time, would you please tell me what you think of the database structure so far? Can you see any troubles I may run into? It's basically a voting system for students. Tables I'm adding are a student_Answer table and possily a student_Result table for Correct Answer and Wrong Answer Total.

 

Or would the concept of student_Results be better as queries? I.e. find the name of the student who has the most correct anwer, I have no idea how to put this into a query on the spot, but I'd be using  the Answer_No in student_Result against question_Correct_Answer where question_ID's are the same

 

 

May you also explain

 

LEFT JOIN choice c2 ON question.question_ID = c2.question_ID AND question.question_correct_answer = c2.choice_No

 

to me?

 

 

Thank you very much for your time.

[Barand]

We join twice to the choice table.

 

1 ) join on question_ID to get count

2 ) join on question_ID, choice_No to get match for correct answer

 

Because we join twice, the second needs a table alias (c2) so SQL can distinguish between them.

 

Use a table for the results if you need to keep the results. If the questions and answers change, you won't be able to reproduce the results query at a future time. IF you don't need to store them, use a query.

[Yuki]

Brilliant explenation, thank you!

 

Hmm I don't need to store them, I just need to be able to query them, I.e. find out which student has the most right answers, find out who has the most wrong, work out  RightAnswer/WrongAnswer I.e.  5/9 (5 right outof 9 questions) and work out Average scores or average % chance of a student getting the question right based on students who have already taken the question.

 

What do you think?

[Barand]

average % chance of a student getting the question right based on students who have already taken the question

 

I'm useless when it comes to probability topics so I'm no help there.

 

As for the rest, you'll probably gets lots of subquery practice.

[Yuki]

Thanks for all your help, was my first post on this board (or any) and I wasn't sure whether I'd get help or not, but you've been great.

 

Could you please remove te Create/Populate sql's from my posts, I don't want any hassel when I hand in my project and it's run through their anti-plagerism search system.

 

[fenway]

Could you please remove te Create/Populate sql's from my posts, I don't want any hassel when I hand in my project and it's run through their anti-plagerism search system.

I guess it's Barand's decision -- but if you _did_ plagerize (which you don't need to), then why pretend that you didn't? It's your homework, not ours... take our advice, but don't copy it.