Join tables with records that may not exist

Question

So, I have the following tables that cause me a headache. What I want to do is create a query that returns all assignments for this course with assignment information (grade, middle class, etc.) Or just null in these fields if the student has not passed or transferred his assignment, I tried left join, but I can not figure out these tables. They are old and poorly built, but all that I have.

CourseInst_Students Register for a student in a course instance

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

Assignments_CourseInst Registers assignments to a course instance

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

Collections

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

Assignments_Solutions. (, ..) ( 1 ). , / .

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

Assignments_Solutions_Groups SSN .

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

SSN :

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

- ? !

UPDATE

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

:

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

Answer 1

, Oracle, ( ). , . 1 , 2 . . , , 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;

GRP_SUBMIT , .

GRP_ASSIGN .

select, GRP_SUBMIT GRP_ASSIGN , .

:

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

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

Answer 2

, ? , MSSQL, MySQL,...?

- ?

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';