NickName:user1009073 Ask DateTime:2020-10-02T02:49:16

Oracle - Finding missing /non-joined records

I have an issue in Oracle 12 that is easiest explained with the traditional database design scenario of students, classes, and students taking classes called registrations. I understand this model well. I have a scenario where I need to get a COMPLETE list, of all students against ALL classes, and whether or not they are taking that class or not...

Lets use this table design here...

CREATE TABLE CLASSES 
(CLASSID  VARCHAR2(10) PRIMARY KEY,
CLASSNAME  VARCHAR2(25),
INSTRUCTOR VARCHAR2(25) );


CREATE TABLE STUDENTS
(STUDENTID  VARCHAR2(10) PRIMARY KEY,
STUDENTNAMENAME  VARCHAR2(25)
STUDY_MAJOR VARCHAR2(25) );

CREATE TABLE REGISTRATION
(
 CLASSID VARCHAR2(10 BYTE), 
 STUDENTID VARCHAR2(10 BYTE), 
 GRADE NUMBER(4,0), 
 CONSTRAINT "PK1" PRIMARY KEY ("CLASSID", "STUDENTID"),
 CONSTRAINT "FK1" FOREIGN KEY ("CLASSID") REFERENCES "CLASSES" ("CLASSID") ENABLE, 
 CONSTRAINT "FK2" FOREIGN KEY ("STUDENTID") REFERENCES "EGR_MM"."STUDENTS" ("STUDENTID") ENABLE
 ) ;

So assume the following... 300 students, and 15 different classes... and the REGISTRATION table will show how many students taking how many classes... What I need is that info PLUS all the NON-TAKEN combinations... i.e. I need a report (SQL statement) that shows ALL possible combinations... i.e. 300 x 15, and then whether that row exists in the registration table...so for example, the output should look like this...

STUDENTID   Class1_GRADE  Class2_Grade      Class3_Grade`       Class4_Grade
101         A               B                   Not Taking          A
102         C               Not Taking          Not Taking          Not Taking
****** THIS STUDENT NOT TAKING ANY CLASSES So NOT in the Registrations Table
103         Not Taking      Not Taking          Not Taking          Not Taking

This would work as well, and I can probably do a PIVOT to get the above listing.

STUDENTID   CLASSID  GRADE
101         Class1    A
101         Class2    B
101         Class3    Not Taking
101         Class4    A
...
102         Class1    C
102         Class2    Not Taking
102         Class3    Not Taking
102         Class4    Not Taking
...
103         Class1    Not Taking  // THIS STUDENT NOT TAKING ANY CLASSES
103         Class2    Not Taking
103         Class3    Not Taking
103         Class4    Not Taking

How do I fill in the missing data, i.e. the combination of students and classes NOT taken...?

Copyright Notice：Content Author:「user1009073」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/64161546/oracle-finding-missing-non-joined-records

Answers

MT0 2020-10-01T20:24:22

CROSS JOIN the students and classes and then LEFT OUTER JOIN the registrations and then use COALESCE to get the Not taken value:\nSELECT s.studentid,\n c.classid,\n COALESCE( TO_CHAR( r.grade ), 'Not taken' ) AS grade\nFROM students s\n CROSS JOIN classes c\n LEFT OUTER JOIN registration r\n ON ( s.studentid = r.studentid AND c.classid = r.classid )\n\nWhich, if you have the data:\nINSERT INTO Classes\nSELECT LEVEL,\n 'Class' || LEVEL,\n 'Instructor' || LEVEL\nFROM DUAL\nCONNECT BY LEVEL <= 3;\n\nINSERT INTO Students\nSELECT TO_CHAR( LEVEL, 'FM000' ),\n 'Student' || LEVEL,\n 'Major'\nFROM DUAL\nCONNECT BY LEVEL <= 5;\n\nINSERT INTO Registration\nSELECT 1, '001', 4 FROM DUAL UNION ALL\nSELECT 1, '002', 2 FROM DUAL UNION ALL\nSELECT 1, '003', 5 FROM DUAL UNION ALL\nSELECT 2, '001', 3 FROM DUAL UNION ALL\nSELECT 3, '001', 1 FROM DUAL;\n\nThen it outputs:\n\n\nSTUDENTID | CLASSID | GRADE \n:-------- | :------ | :--------\n001 | 1 | 4 \n002 | 1 | 2 \n003 | 1 | 5 \n001 | 2 | 3 \n001 | 3 | 1 \n005 | 1 | Not taken\n004 | 2 | Not taken\n003 | 3 | Not taken\n005 | 3 | Not taken\n005 | 2 | Not taken\n002 | 2 | Not taken\n003 | 2 | Not taken\n004 | 1 | Not taken\n002 | 3 | Not taken\n004 | 3 | Not taken\n\n\n\nIf you want to pivot it then:\nSELECT *\nFROM (\n SELECT s.studentid,\n c.classid,\n COALESCE( TO_CHAR( r.grade ), 'Not taken' ) AS grade\n FROM students s\n CROSS JOIN classes c\n LEFT OUTER JOIN registration r\n ON ( s.studentid = r.studentid AND c.classid = r.classid )\n)\nPIVOT ( MAX( grade ) FOR classid IN (\n 1 AS Class1,\n 2 AS Class2,\n 3 AS Class3\n) )\nORDER BY StudentID\n\nWhich outputs:\n\n\nSTUDENTID | CLASS1 | CLASS2 | CLASS3 \n:-------- | :-------- | :-------- | :--------\n001 | 4 | 3 | 1 \n002 | 2 | Not taken | Not taken\n003 | 5 | Not taken | Not taken\n004 | Not taken | Not taken | Not taken\n005 | Not taken | Not taken | Not taken\n\n\ndb<>fiddle here",