Joining tables with records that might not exist

Question

So I have these following tables that are causing me a bit of a headache. What I want to do is to create a query that returns all assignments for a given course with a assignment info (grade, average grade etc.) or just null in those fields if the student did not hand in or has not handed in his assignment. I have tried left join but I can't manage to figure it out for those tables. They are old and badly constructed but it's all I have.

CourseInst_Students Holds a registration for a student in an instance of a course

| ID_CourseInst| SSN                 |
| =============|=====================|
| 24744        | 080590-XXXX         | my ssn

Assignments_CourseInst Registers assignments to an instance of a course

| ID_Assignment| ID_CourseInst       |
| =============|=====================|
| 37978        | 24744               |
| 37979        | 24744               |
| 37992        | 24744               |
| 38046        | 24744               |

Assignments

 | ID_Assignment| Title                     |
 | =============|===========================|
 | 37978        | Og þá var kátt í höllinni |
 | 37979        | Test for Assignments      |
 | 37992        | Test 2                    |
 | 38046        | Eitthvað gott verkefni    |

Assignments_Solutions Holds an info (grade, handin date etc.) for a group of students (1 to many). No record if the student/'s have not handed in their solutions.

 | ID_Assignment| ID_Group                  | Grade   |
 | =============|===========================|=========|
 | 37978        | 808046                    | 10      | only one group has handed in.

Assignments_Solutions_Groups Registers student's SSN to a group.

| ID_Group     | SSN                       |
| =============|===========================|
| 808046       | 221180-XXXX               | not my SSN (see mine above)

My preferred results when searching for assignments for my SSN would be something like:

| ID_Assignment| Title                     | Grade   |
| =============|===========================|=========|
| 37978        | Og þá var kátt í höllinni | NULL    |
| 37979        | Test for Assignments      | NULL    |
| 37992        | Test 2                    | NULL    |
| 38046        | Eitthvað gott verkefni    | NULL    |

Can anybody figure this out ? Thanks!

UPDATE

Here is what I've got so far

select A.ID_Assignment, A.Title, ASo.Grade
from CourseInst_Students as CS
join Assignments_CourseInst as AC on CS.ID_CourseInst= AC.ID_CourseInst
join Assignments as A on AC.ID_Assignment = A.ID_Assignment
left join Assignments_Solutions as ASo on A.ID_Assignment = ASo.ID_Assignment
left join Assignments_Solutions_Groups as ASGs on ASo.ID_Group = ASGs.ID_Group
where CS.SSN = '080590-XXXX'
      AND CS.id_namskeid = 24744

and I get the following results:

| ID_Assignment| Title                     | Grade   |
| =============|===========================|=========|
| 37978        | Og þá var kátt í höllinni | 10      | <- this is someone else's handin. I want NULL here
| 37979        | Test for Assignments      | NULL    | 
| 37992        | Test 2                    | NULL    |
| 38046        | Eitthvað gott verkefni    | NULL    |

score 1 · Accepted Answer · answered Nov 07 '13 at 13:59

Assuming it is Oracle, The problem is with the design (absolutely rubbish). A student cannot submit if he has not enrolled. In your case student 1 has enrolled but student 2 has submitted. You will end up in Cartesian product to achieve the desired result. Ask the person who designed this, to read the basics of SQL.

WITH C_S
    AS (SELECT
             '24744' AS ID_COURSEINST,
             '080590-XXXX' AS SSN
        FROM
             DUAL
        UNION ALL
        SELECT
             '24744' AS ID_COURSEINST,
             '221180-XXXX' AS SSN
        FROM
             DUAL),
    A_C
    AS (SELECT
             '37978' AS ID_ASSIGNMENT,
             '24744' AS ID_COURSEINST
        FROM
             DUAL
        UNION ALL
        SELECT
             '37979' AS ID_ASSIGNMENT,
             '24744' AS ID_COURSEINST
        FROM
             DUAL
        UNION ALL
        SELECT
             '37992' AS ID_ASSIGNMENT,
             '24744' AS ID_COURSEINST
        FROM
             DUAL
        UNION ALL
        SELECT
             '38046' AS ID_ASSIGNMENT,
             '24744' AS ID_COURSEINST
        FROM
             DUAL),
    ASSI
    AS (SELECT
             '37978' AS ID_ASSIGNMENT,
             'Og þá var kátt í höllinni' AS TITLE
        FROM
             DUAL
        UNION ALL
        SELECT
             '37979' AS ID_ASSIGNMENT,
             'Test for Assignments' AS TITLE
        FROM
             DUAL
        UNION ALL
        SELECT
             '37992' AS ID_ASSIGNMENT,
             'Test 2' AS TITLE
        FROM
             DUAL
        UNION ALL
        SELECT
             '38046' AS ID_ASSIGNMENT,
             'Eitthvað gott verkefni' AS TITLE
        FROM
             DUAL),
    A_S
    AS (SELECT
             '37978' AS ID_ASSIGNMENT,
             '808046' AS ID_GROUP,
             '10' AS GRADE
        FROM
             DUAL),
    A_S_G
    AS (SELECT
             '808046' AS ID_GROUP,
             '221180-XXXX' AS SSN
        FROM
             DUAL),
    GRP_SUBMIT
    AS (SELECT
             A_S.ID_ASSIGNMENT ID_ASSIGNMENT_AS,
             A_S.ID_GROUP ID_GROUP_AS,
             A_S.GRADE,
             C_S.ID_COURSEINST COURSE_SUB,
             C_S.SSN SUBMIT_SSN
        FROM
             C_S,
             A_S,
             A_S_G
        WHERE
             A_S_G.SSN = C_S.SSN
             AND A_S_G.ID_GROUP = A_S.ID_GROUP),
    GRP_ASSIGN
    AS (SELECT
             A_C.ID_ASSIGNMENT ID_ASSIGNMENT_AC,
             A_C.ID_COURSEINST AS ID_COURSEINST_AC,
             ASSI.ID_ASSIGNMENT ID_ASSIGNMENT_ASSI,
             ASSI.TITLE TITLE_ASSI,
             C_S.ID_COURSEINST COURSE_ASSI,
             C_S.SSN ASSI_SSN
        FROM
             A_C,
             ASSI,
             C_S
        WHERE
             A_C.ID_ASSIGNMENT = ASSI.ID_ASSIGNMENT)
SELECT
      ID_ASSIGNMENT_ASSI,
      TITLE_ASSI,
      GRADE
FROM
      (SELECT
            *
       FROM
                GRP_SUBMIT
            RIGHT OUTER JOIN
                GRP_ASSIGN
            ON ID_ASSIGNMENT_AS = ID_ASSIGNMENT_AC
               AND SUBMIT_SSN = ASSI_SSN)
WHERE
      ASSI_SSN = '221180-XXXX'
      AND COURSE_ASSI = 24744
ORDER BY
      ID_ASSIGNMENT_ASSI;

In GRP_SUBMIT, I check the number of students submitted the assignments.

In GRP_ASSIGN, I manipulate the expected number of submissions by cartesian product.

In the final select, I make GRP_SUBMIT to have a right outer join on GRP_ASSIGN inside the subquery and then filtering based on your search.

Results:

Input: 221180-XXXX

ID_ASSIGNMENT_ASSI TITLE_ASSI                GRADE
------------------ ------------------------- -----
37978              Og þá var kátt í höllinni 10   
37979              Test for Assignments           
37992              Test 2                         
38046              Eitthvað gott verkefni         

4 rows selected.

Input: 080590-XXXX

ID_ASSIGNMENT_ASSI TITLE_ASSI                GRADE
------------------ ------------------------- -----
37978              Og þá var kátt í höllinni    
37979              Test for Assignments           
37992              Test 2                         
38046              Eitthvað gott verkefni         

4 rows selected.

Hah, no argue there about the design of the tables. Thanks for the response, it helped me see how I can do this in seperate queries. — Bjarki, Nov 08 '13 at 00:07

score 0 · Answer 2 · answered Nov 07 '13 at 02:27

0

Can you share the queries you've tried so far? Also, are you using MSSQL, MySQL, ...?

Have you tried something like this?

select a.ID_Assignment, a.Title, asol.Grade from Assignments a
        left join Assignments_Solutions asol 
        on asol.ID_Assigment = a.ID_Assignment
        left join Assignments_Solutions_Groups asg
        on asg.ID_Group = asol.ID_Group
        where asg.SSN = 'yourSocialSecurityNumber';

answered Nov 07 '13 at 02:27

napo

869
9
19

This gives me no results. I've updated my question with the query I've got so far. Thanks for the response – Bjarki Nov 07 '13 at 11:00

Joining tables with records that might not exist

2 Answers2