Joining tables and finding difference

Question

I have a table which contains the following schema:

Table1
    +------------------+--------------------+-------------------+-------------+-------------+
    |student_id|project_id|name|project_name|approved|evaluation_type|grade| cohort_number|

I have another table with the following:

Table2
    +-------------+----------+
    |cohort_number|project_id|

My problem is: I want to get for each student_id the projects that he has not completed (no rows). The way i know all the projects he should have done is by checking the cohort_number. Basically I need the "diference" between the 2 tables. I want to fill table 1 with the missing entries, by comparing with table 2 project_id for that cohort_number.

I am not sure if I was clear. I tried using LEFT JOIN, but I only get records where it matches. (I need the opposite)

Example:

Table1

    |student_id|project_id|name|           project_name| approved|evaluation_type|             grade|cohort_number|
    +----------+----------+--------------------+------+--------------------+--------+---------------+------------------
    |        13|        18|Name|  project/sd-03-bloc...|    true|       standard|               1.0|            3|
    |        13|         7|Name|  project/sd-03-bloc...|    true|       standard|               1.0|            3|
    |        13|        27|Name|  project/sd-03-bloc...|    true|       standard|               1.0|            3|

Table2

+-------------+----------+
|cohort_number|project_id|
+-------------+----------+
|            3|        18|
|            3|        27|
|            4|        15|
|            3|         7|
|            3|        35|

I want:

    |student_id|project_id|name|           project_name| approved|evaluation_type|             grade|cohort_number|
    +----------+----------+--------------------+------+--------------------+--------+---------------+------------------
    |        13|        18|Name|  project/sd-03-bloc...|    true|       standard|               1.0|            3|
    |        13|         7|Name|  project/sd-03-bloc...|    true|       standard|               1.0|            3|
    |        13|        27|Name|  project/sd-03-bloc...|    true|       standard|               1.0|            3|
    |        13|        35|Name|  project/sd-03-bloc...|    false|       standard|                 0|            3|

Thanks in advance

Please provide sample data and desired results to clarify your requirement. — GMB, May 21 '20 at 23:56
So, for a given student in `table1`, columns `name`, `project_name`, `evaluation_type` and `cohort_number` are always the same? — GMB, May 22 '20 at 00:09
name and cohort_number yes. but project_name and evaluation_type no. To be more clear, project_name comes from Table 2, but i removed for simplification — Lucca Zenobio, May 22 '20 at 00:10
Does this answer your question? [SQL - find records from one table which don't exist in another](https://stackoverflow.com/questions/367863/sql-find-records-from-one-table-which-dont-exist-in-another) — philipxy, May 22 '20 at 00:56

score 1 · Accepted Answer · answered May 22 '20 at 00:12

If I followed you correctly, you can get all distinct (student_id, cohort_number, name) tuples from table1, and then bring all corresponding rows from table2. This basically gives you one row for each project that a student should have completed.

You can then bring table1 with a left join. "Missing" projects are identified by null values in columns project_name, approved, evaluation_type, grade.

select 
    s.student_id,
    t2.project_id,
    s.name,
    t1.project_name,
    t1.approved,
    t1.evaluation_type,
    t1.grade,
    s.cohort_number
from (select distinct student_id, cohort_number, name from table1) s
inner join table2 t2 
    on  t2.cohort_number = s.cohort_number
left join table1 t1
    on  t1.student_id = s.student_id 
    and t1.project_id = t.project_id

Joining tables and finding difference

1 Answers1