include results from table not found in another table

  • 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

  • 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?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • 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

  • 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

  • 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.

  • 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