AT-2016 AT-2016 - 3 months ago 8
SQL Question

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

Answer

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