Yuki Posted March 16, 2008 Share Posted March 16, 2008 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; Quote Link to comment Share on other sites More sharing options...
Barand Posted March 16, 2008 Share Posted March 16, 2008 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; Quote Link to comment Share on other sites More sharing options...
Yuki Posted March 16, 2008 Author Share Posted March 16, 2008 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" Quote Link to comment Share on other sites More sharing options...
Barand Posted March 16, 2008 Share Posted March 16, 2008 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 Quote Link to comment Share on other sites More sharing options...
Yuki Posted March 16, 2008 Author Share Posted March 16, 2008 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted March 16, 2008 Share Posted March 16, 2008 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; Quote Link to comment Share on other sites More sharing options...
Yuki Posted March 16, 2008 Author Share Posted March 16, 2008 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted March 16, 2008 Share Posted March 16, 2008 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. Quote Link to comment Share on other sites More sharing options...
Yuki Posted March 16, 2008 Author Share Posted March 16, 2008 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? Quote Link to comment Share on other sites More sharing options...
Barand Posted March 16, 2008 Share Posted March 16, 2008 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. Quote Link to comment Share on other sites More sharing options...
Yuki Posted March 16, 2008 Author Share Posted March 16, 2008 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 17, 2008 Share Posted March 17, 2008 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.