Jump to content

JOIN Query issue


Yuki

Recommended Posts

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;

 

 

Link to comment
Share on other sites

 

 

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.

 

 

Link to comment
Share on other sites

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;

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.