CODEWITHSUNDEEP
sqlsql-server
AT-2017
AT-2017

Reputation: 3149

Make Use of Full Outer Join With The Sql Query

I've an issue with Sql query. So I am posting the table structures first:

Table structure - StudentGrade

ID - Department - StudentID - Grade - Course
--------------------------------------------
 1      2            2002      A+      102
 2      2            2002      B+      102
 3      2            2002      A+      104

The output I was supposed to get is the following:

ID - Department - StudentID - Grade - Course
----------------------------------------------
 2      2           2002        B+     102    // as this was inserted last
 3      2           2002        A+     104

And I obtained the above result using the following query that works fine only if there are results stored in the StudentGrade table:

SELECT 
    COALESCE(m.Grade, 'Not Graded Yet') AS Grade 
FROM 
    StudentGrade m
FULL OUTER JOIN 
    EnrollCourse k ON k.Course = m.Course
WHERE 
    m.ID IN (SELECT MAX(m.ID) 
             FROM StudentGrade m
             GROUP BY m.StudentID, m.Course) 
    AND m.StudentID = 2002    // this query returns the grade that is inserted lastly for a course and for a specific student

By the way, the table structure for EnrollCourse is as follows:

ID - StudentID - Course - EnrollDate
-------------------------------------
 1      2002       102    NULL
 2      2002       104    NULL

The results are eligible for the enrolled students I mean students that are enrolled in a course. So now my requirement is, suppose, StudentID 2002 is enrolled to two courses. If no grade is assigned to the student, then the output I expect from the StudentGrade table:

ID - Department - StudentID - Grade -          Course
------------------------------------------------------
 1        2          2002     No Grade Yet       102
 2        2          2002     No Grade Yet       104

If assigned for the course 102, then it would be as follows:

ID - Department - StudentID - Grade -       Course
---------------------------------------------------
 1       2           2002     B+              102
 2       2           2002     No Grade Yet    104

Again, if the course grade is updated like A+, then it will be:

ID - Department - StudentID - Grade -          Course
------------------------------------------------------
 1        2          2002     A+                 102
 2        2          2002     No Grade Yet       104

There are some INNER JOIN in the query. So I tried to use the following (Just modified the above that seems to be not working):

SELECT 
    COALESCE(m.Grade, 'Not Graded Yet') AS Grade 
FROM 
    StudentGrade m
FULL OUTER JOIN 
    EnrollCourse k ON k.Course = m.Course
WHERE 
    m.ID IN (SELECT MAX(m.ID) 
             FROM StudentGrade m
             FULL OUTER JOIN EnrollCourse k ON k.Course = m.Course
             GROUP BY m.StudentRegNo, m.Course) 
    AND m.StudentID = 2002

Upvotes: 0

Views: 43

Answers (1)

user6638270
user6638270

Reputation:

Firstly you either need Department in EnrollCourse or you need a table to link Department to Course. For my example, I have taken the easy way, and added Department to EnrollCourse

DECLARE @enrollCourse TABLE (
ID int,
StudentID int,
Course int,
Department int
)

DECLARE @studentGrade TABLE (
ID int,
Department int,
StudentID int,
Grade varchar(2),
Course int
)

INSERT INTO @enrollCourse VALUES (1, 2002, 102, 2)
INSERT INTO @enrollCourse VALUES (2, 2002, 104, 2)

SELECT ec.ID, ec.Department, ec.StudentID, COALESCE(sG.Grade, 'Not Graded Yet') as Grade, eC.Course FROM @enrollCourse eC left join 
(SELECT ID, Department, StudentID, Grade, Course FROM @studentGrade s INNER JOIN (SELECT MAX(ID) as MaxID FROM @studentGrade GROUP BY Department, StudentID, Course) mG ON s.ID = mG.MaxID)  sG ON
ec.StudentID = sG.StudentID and ec.Course = sg.Course and ec.Department = sg.Department 

INSERT INTO @studentGrade VALUES(1, 2, 2002, 'B+', 102) 

SELECT ec.ID, ec.Department, ec.StudentID, COALESCE(sG.Grade, 'Not Graded Yet') as Grade, eC.Course FROM @enrollCourse eC left join 
(SELECT ID, Department, StudentID, Grade, Course FROM @studentGrade s INNER JOIN (SELECT MAX(ID) as MaxID FROM @studentGrade GROUP BY Department, StudentID, Course) mG ON s.ID = mG.MaxID)  sG ON
ec.StudentID = sG.StudentID and ec.Course = sg.Course and ec.Department = sg.Department 

DELETE FROM @studentGrade

INSERT INTO @studentGrade VALUES(1, 2, 2002, 'A+', 104) 

SELECT ec.ID, ec.Department, ec.StudentID, COALESCE(sG.Grade, 'Not Graded Yet') as Grade, eC.Course FROM @enrollCourse eC left join 
(SELECT ID, Department, StudentID, Grade, Course FROM @studentGrade s INNER JOIN (SELECT MAX(ID) as MaxID FROM @studentGrade GROUP BY Department, StudentID, Course) mG ON s.ID = mG.MaxID)  sG ON
ec.StudentID = sG.StudentID and ec.Course = sg.Course and ec.Department = sg.Department 

INSERT INTO @studentGrade VALUES(2, 2, 2002, 'B+', 102) 

SELECT ec.ID, ec.Department, ec.StudentID, COALESCE(sG.Grade, 'Not Graded Yet') as Grade, eC.Course FROM @enrollCourse eC left join 
(SELECT ID, Department, StudentID, Grade, Course FROM @studentGrade s INNER JOIN (SELECT MAX(ID) as MaxID FROM @studentGrade GROUP BY Department, StudentID, Course) mG ON s.ID = mG.MaxID)  sG ON
ec.StudentID = sG.StudentID and ec.Course = sg.Course and ec.Department = sg.Department 

INSERT INTO @studentGrade VALUES(3, 2, 2002, 'B+', 104) 

SELECT ec.ID, ec.Department, ec.StudentID, COALESCE(sG.Grade, 'Not Graded Yet') as Grade, eC.Course FROM @enrollCourse eC left join 
(SELECT ID, Department, StudentID, Grade, Course FROM @studentGrade s INNER JOIN (SELECT MAX(ID) as MaxID FROM @studentGrade GROUP BY Department, StudentID, Course) mG ON s.ID = mG.MaxID)  sG ON
ec.StudentID = sG.StudentID and ec.Course = sg.Course and ec.Department = sg.Department

Upvotes: 1

Related Questions