April 7, 2016 at 8:00 am
I have this query:
SELECT Student, Question, Answer, AnswerId AS Selected, isCorrect
FROM student_quiz_session_data
It works ok, but I also needed to get all the unselected answers that the student didn't pick.
I have another table where I can get all the answers for a quiz but I'm not sure how to join/include it into my query above.
select Answer AS Unselected FROM quiz_data
Ideally, I'm trying to get a result-set that looks like this:
SELECT 'Jimmy' AS Student,'What is your favorite color?' As Question, 'Blue' AS Answer, 1 AS Selected, 0 AS Unselected, 1 AS isCorrect
UNION ALL SELECT 'Jimmy','The Moon is made out of cheese(T or F).', 'True', 1, 0, 0
UNION ALL SELECT 'Jimmy','The Moon is made out of cheese(T or F).', 'False', 0, 1, NULL
UNION ALL SELECT 'Jimmy','Which item(s) is/are part of a computer?', 'CPU', 1, 0, 1
UNION ALL SELECT 'Jimmy','Which item(s) is/are part of a computer?', 'Hard Drive', 1, 0, 1
UNION ALL SELECT 'Jimmy','Which item(s) is/are part of a computer?', 'Video Card', 1, 0, 1
UNION ALL SELECT 'Jimmy','Which item(s) is/are part of a computer?', 'Wheel', 1, 0, 0
UNION ALL SELECT 'Jimmy','Which item(s) is/are part of a computer?', 'Case', 0, 1, 0
UNION ALL SELECT 'Jimmy','Which item(s) is/are part of a computer?', 'Brake Pedal', 1, 0, 0
UNION ALL SELECT 'Alex','What is your favorite color?', 'Green', 1, 0, 1
UNION ALL SELECT 'Alex','The Moon is made out of cheese(T or F).', 'True', 0, 1, NULL
UNION ALL SELECT 'Alex','The Moon is made out of cheese(T or F).', 'False', 1, 0, 1
UNION ALL SELECT 'Alex','Which item(s) is/are part of a computer?', 'CPU', 0, 1, 0
UNION ALL SELECT 'Alex','Which item(s) is/are part of a computer?', 'Hard Drive', 0, 1, 0
UNION ALL SELECT 'Alex','Which item(s) is/are part of a computer?', 'Video Card', 1, 0, 1
UNION ALL SELECT 'Alex','Which item(s) is/are part of a computer?', 'Wheel', 1, 0, 0
UNION ALL SELECT 'Alex','Which item(s) is/are part of a computer?', 'Case', 1, 0, 1
UNION ALL SELECT 'Alex','Which item(s) is/are part of a computer?', 'Brake Pedal', 1, 0, 0
UNION ALL SELECT 'Kate','What is your favorite color?', 'Red', 1, 0, 1
UNION ALL SELECT 'Kate','The Moon is made out of cheese(T or F).', 'True', 0, 1, NULL
UNION ALL SELECT 'Kate','The Moon is made out of cheese(T or F).', 'False', 1, 0, 1
UNION ALL SELECT 'Kate','Which item(s) is/are part of a computer?', 'CPU', 1, 0, 1
UNION ALL SELECT 'Kate','Which item(s) is/are part of a computer?', 'Hard Drive', 1, 0, 1
UNION ALL SELECT 'Kate','Which item(s) is/are part of a computer?', 'Video Card', 1, 0, 1
UNION ALL SELECT 'Kate','Which item(s) is/are part of a computer?', 'Wheel', 0, 1, 1
UNION ALL SELECT 'Kate','Which item(s) is/are part of a computer?', 'Case', 1, 0, 1
UNION ALL SELECT 'Kate','Which item(s) is/are part of a computer?', 'Brake Pedal', 0, 1, 1
April 7, 2016 at 8:31 am
It's hard to tell without knowing how your two tables are related. Can you post the table DDL and also mock up the sample data that matches your desired output?
April 7, 2016 at 10:13 am
As John Rowan said, this is quite hard without knowing your table/data format.
If my understanding is correct, and you're looking to return a full list of questions, students, and their answers regardless of if they did answer, my thoughts would be to start at the Question table first (if you're data is set up that way).
So select your data from the questions table, and join onto the student table (I say join, but i imagine you'll be cross applying if you want to get every result from both tables), and then left join to your answers table.
If this doesn't make sense or help for your set up, then please post more information. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 7, 2016 at 1:58 pm
you'll also have to include the question from the quiz_data table or else you'll be using the same (unselected) answers on all the questions.
My guess follows, not tested however!
SELECT Student, Question, Answer, AnswerId AS Selected, isCorrect
FROM student_quiz_session_data
UNION
SELECT anstab.Student, anstab.Question, quiztab.Answer, 0 AS Selected, null as isCorrect
FROM student_quiz_session_datA anstab
JOIN quiz_data qiztab
on anstab.question = qiztab.question and
anstab.answer <> qiztab.answer
April 7, 2016 at 2:12 pm
This sounds like it's an outer join of some sort. Certainly Patrick's answer might work better, and allow better indexing.
What I'd suggest if you give us the DDL for CREATING the tables and some INSERT statements for getting a test set up. Show an example of what you need returned. Make sure you cover a couple cases.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 7, 2016 at 2:34 pm
You student_quiz_session_data table represents a many-to-many relation between your students and your quiz_data and is a subset of all possible pairings of your students and your quiz_data. It is impossible to guarantee that you're getting the complete superset from a subset, because you will miss any students that did not answer any questions and miss any questions that no student answered. The only way to guarantee the complete superset is to get the Cartesian product of the students and the quiz_data and then match those with the student_quiz_session_data. In other words,
SELECT *
FROM students s
CROSS JOIN quiz_data qd
LEFT OUTER JOIN student_quiz_session_data sqsd
ON s.student_id = sqsd.student_id
AND qd.question = sqsd.question
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply