How to compare a "Plan" table against a "Completed" table to match specific rows, and also utilize a 3rd table as a lookup in some cases

Question

I'm fairly new to SQL and completely self-taught, so I apologize ahead of time! My situation is we have a table that contains a student's plan of courses they should take for each quarter (table E) and a table of courses a student has actually taken (table R).

TABLE E

EMPLID | ID | QTR  | SUBJ    | COURSE_NUM
11111  | 1  | 2213 | HCML    | 325
11111  | 1  | 2213 | HPM     | 340
11111  | 1  | 2215 | CMST    | 330
22222  | 1  | 2213 | ART     | 110
22222  | 1  | 2213 | GENERAL | 1
22222  | 1  | 2213 | GENERAL | 5

TABLE R

EMPLID | QTR  | SUBJ    | COURSE_NUM
11111  | 2213 | CMST    | 330
11111  | 2213 | HCML    | 325
11111  | 2215 | HPM     | 340
22222  | 2213 | ART     | 105
22222  | 2213 | ENGL    | 201
22222  | 2213 | ASTR&   | 101

In table E there can also be placeholder courses listed as "GENERAL" that can map to a large set of courses defined in table G. So if a student has "GENERAL 1" on their plan, and they take any of the courses in table G marked with a 1, it should count as a match.

TABLE G

SUBJ  | NUM | MAPPING
ENGL& | 101 |   1
ENGL  | 201 |   1
ENGL& | 235 |   1
ANTH& | 215 |   5
ASTR& | 101 |   5
BIOL  | 108 |   5

I've managed to make the first part work, but I'm completely stuck on how I would do the mapping of these GENERAL courses. Instead of returning PLACEHOLDER in the final column, I want it to show a MATCH or MISMATCH. Do I do some sort of a conditional join on the other table?

DESIRED RESULTS

EMPLID | ID | QTR  | E.SUBJ | E.COURSE_NUM | R.SUBJ | R.COURSE_NUM | FOLLOW_PLAN
11111  | 1  | 2213 | HCML    | 325         | HCML   | 325          | MATCH
11111  | 1  | 2213 | HPM     | 340         | NULL   | NULL         | MISMATCH
11111  | 1  | 2215 | CMST    | 330         | NULL   | NULL         | MISMATCH
22222  | 1  | 2213 | ART     | 110         | NULL   | NULL         | MISMATCH
22222  | 1  | 2213 | GENERAL | 1           | ENGL   | 201          | MATCH
22222  | 1  | 2213 | GENERAL | 5           | ASTR&  | 101          | MATCH

Here is a sample of the code I have working now:

CREATE TABLE PlanCourses (
  EMPLID int
  ,ID int
  ,QTR int
  ,SUBJ varchar(7)
  ,COURSE_NUM int
  );

INSERT INTO PlanCourses
  VALUES
  (11111,1,2213,'HCML',325)
  ,(11111,1,2213,'HPM',340)
  ,(11111,1,2215,'CMST',330)
  ,(22222,1,2213,'ART',110)
  ,(22222,1,2213,'GENERAL',1)
  ,(22222,1,2213,'GENERAL',5);

CREATE TABLE CourseWork(
  EMPLID int
  ,QTR int
  ,SUBJ varchar(7)
  ,COURSE_NUM int
  );
  
INSERT INTO CourseWork
  VALUES
  (11111,2213,'CMST',330)
  ,(11111,2213,'HCML',325)
  ,(11111,2215,'HPM',340)
  ,(22222,2213,'ART',110)
  ,(22222,2213,'ENGL',201)
  ,(22222,2213,'ASTR&',101);

CREATE TABLE PlaceholderCourse (
  SUBJ varchar(7)
  ,NUM int
  ,MAPPING int);
  
INSERT INTO PlaceholderCourse
  VALUES
  ('ENGL&',101,1)
  ,('ENGL',201,1)
  ,('ENGL&',235,1)
  ,('ANTH&',215,5)
  ,('ASTR&',101,5)
  ,('BIOL',108,5);
GO

18 rows affected

SELECT * FROM PlanCourses;
SELECT * FROM CourseWork;
SELECT * FROM PlaceholderCourse;
GO

EMPLID | ID |  QTR | SUBJ    | COURSE_NUM
-----: | -: | ---: | :------ | ---------:
 11111 |  1 | 2213 | HCML    |        325
 11111 |  1 | 2213 | HPM     |        340
 11111 |  1 | 2215 | CMST    |        330
 22222 |  1 | 2213 | ART     |        110
 22222 |  1 | 2213 | GENERAL |          1
 22222 |  1 | 2213 | GENERAL |          5

EMPLID |  QTR | SUBJ  | COURSE_NUM
-----: | ---: | :---- | ---------:
 11111 | 2213 | CMST  |        330
 11111 | 2213 | HCML  |        325
 11111 | 2215 | HPM   |        340
 22222 | 2213 | ART   |        110
 22222 | 2213 | ENGL  |        201
 22222 | 2213 | ASTR& |        101

SUBJ  | NUM | MAPPING
:---- | --: | ------:
ENGL& | 101 |       1
ENGL  | 201 |       1
ENGL& | 235 |       1
ANTH& | 215 |       5
ASTR& | 101 |       5
BIOL  | 108 |       5

SELECT
    E.EMPLID
    ,E.ID
    ,E.QTR
    ,E.SUBJ
    ,E.COURSE_NUM
    ,R.SUBJ
    ,R.COURSE_NUM
    ,CASE 
        WHEN E.SUBJ = 'GENERAL'
        THEN 'PLACEHOLDER'
        WHEN R.SUBJ IS NOT NULL
        THEN 'Match'
        ELSE 'Mismatch'
        END AS FOLLOW_PLAN
    
FROM 
    PLANCOURSES E

    LEFT JOIN COURSEWORK R 
        ON R.EMPLID = E.EMPLID
        AND R.QTR = E.QTR
        AND R.SUBJ = E.SUBJ
        AND R.COURSE_NUM = E.COURSE_NUM
GO

EMPLID | ID |  QTR | SUBJ    | COURSE_NUM | SUBJ | COURSE_NUM | FOLLOW_PLAN
-----: | -: | ---: | :------ | ---------: | :--- | ---------: | :----------
 11111 |  1 | 2213 | HCML    |        325 | HCML |        325 | Match      
 11111 |  1 | 2213 | HPM     |        340 | null |       null | Mismatch   
 11111 |  1 | 2215 | CMST    |        330 | null |       null | Mismatch   
 22222 |  1 | 2213 | ART     |        110 | ART  |        110 | Match      
 22222 |  1 | 2213 | GENERAL |          1 | null |       null | PLACEHOLDER
 22222 |  1 | 2213 | GENERAL |          5 | null |       null | PLACEHOLDER

db<>fiddle here

Answer 1

First you'll expand the plan against the list of possible general courses. After that you do a conditional join (as you anticipated) based on the matching criteria. Then remove the extra/potential general courses that didn't contribute any actually completions:

select *,
    case when r.COURSE_NUM is null then 'MISMATCH' else 'MATCH' end as FOLLOW_PLAN
from
    PLANCOURSES e
    left outer join GENERAL g
        on e.SUBJ = 'GENERAL' and g.MAPPING = e.COURSE_NUM
    left outer join COURSEWORK r    
        on  r.EMPLID = e.EMPLID and r.QTR = e.QTR and (
                e.SUBJ <> 'GENERAL' and r.SUBJ = e.SUBJ and r.COURSE_NUM = e.COURSE_NUM
            or  e.SUBJ  = 'GENERAL' and r.SUBJ = g.SUBJ and r.COURSE_NUM = g.NUM
        )
where (g.NUM is null or r.COURSE_NUM is not null);
        

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=347687771a1d888762d5dfcef8b9e9f0

It sounds like there's a possibility of mapping multiple completed courses on to the same general plan slot. Not sure if that would be an issue.

Assuming that none of the courses numbers correspond to mapping numbers you could collapse the or logic in the join condition a little bit. I doubt there's any benefit and the longer version is probably clearer:

-- pay attention to the order of the matches
and r.SUBJ       = COALESCE(g.SUBJ, e.SUBJ)
and r.COURSE_NUM = COALESCE(g.NUM,  e.COURSE_NUM)

Note also that these are equivalent in case you have a preference:

and     (g.NUM is null      or r.COURSE_NUM is not null)
and not (g.NUM is not null and r.COURSE_NUM is null)

You can also use outer apply:

select e.EMPLID, e.ID, e.QTR,
    case when e.SUBJ = 'GENERAL'
        then concat('GENERAL ', cast(e.COURSE_NUM as varchar(2)),
                    concat(' (' + gr.SUBJ + ')', ''))
        else e.SUBJ end as SUBJ,
    coalesce(rr.COURSE_NUM, gr.COURSE_NUM) as COURSE_NUM,
    case when coalesce(rr.COURSE_NUM, gr.COURSE_NUM) is null
        then 'MISMATCH' else 'MATCH' end as FOLLOW_PLAN,
    GMAP_NUM
from
    PLANCOURSES e
    outer apply (
        select r.SUBJ, r.COURSE_NUM,
            row_number() over (
                partition by r.EMPLID, r.QTR, g.MAPPING
                order by r.COURSE_NUM, r.SUBJ) as GMAP_NUM
        from GENERAL g inner join COURSEWORK r
            on  r.SUBJ = g.SUBJ and r.COURSE_NUM = g.NUM        
        where e.SUBJ = 'GENERAL'
            and r.EMPLID = e.EMPLID and r.QTR = e.QTR
            and g.MAPPING = e.COURSE_NUM
    ) gr
    outer apply (
        select r.COURSE_NUM
        from COURSEWORK r
        where e.SUBJ <> 'GENERAL'
            and r.EMPLID = e.EMPLID and r.QTR = e.QTR
            and r.SUBJ = e.SUBJ and r.COURSE_NUM = e.COURSE_NUM
    ) rr
where coalesce(gr.GMAP_NUM, 1) = 1;

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=cbfb7bbddcac7224352a5009c32ab8dc