Hi guys,
I'm trying to build a query that involves two tables, the course table and the studentsLink table. The StudentsLink table describes the link between students and the course. The tables are as below;
Course
courseID(bigint) - PK
courseName (varchar)
courseInstructor (varchar)
StudentsLink
courseID(bigint) - PK
StudentID(bigint) - PK
Below is some sample data;
course table
ID | courseName| courseInstructor
----------------------------------
1 | Algebra 1 | Mike
----------------------------------
2 | English 2 | James
----------------------------------
3 | English 3 | John
----------------------------------
4 | Algebra 2 | Mike
----------------------------------
5 | History 1 | Tony
----------------------------------
Studentlink table
studentID | courseID
----------------------
100 | 2
----------------------
101 | 3
----------------------
102 | 3
----------------------
102 | 4
----------------------
103 | 4
----------------------
100 | 1
----------------------
103 | 3
----------------------
103 | 2
----------------------
The desired outcome is as below given if I was looking for student number 103
ID | courseName| courseInstructor |StudentID | CourseID
---------------------------------------------------------
1 | Algebra 1 | Mike | NULL | NULL
---------------------------------------------------------
2 | English 2 | James | 103 | 2
---------------------------------------------------------
3 | English 3 | John | 103 | 3
---------------------------------------------------------
4 | Algebra 2 | Mike | 103 | 4
---------------------------------------------------------
5 | History 1 | Tony | NULL | NULL
---------------------------------------------------------
The query that I have so far is as below;
SELECT *
FROM course
LEFT JOIN studentLink
ON course.courseID = studentLink.courseID
WHERE studentLink.studentID = 103 OR (studentLink .studentID IS NULL AND studentLink.courseID IS NULL)
ORDER BY studentLink.courseID DESC
I'm basically trying to get a result set of out all the courses available, which one is the particular student registered in and which one is he not so I will be able to display it as a course which we can offer to the student.
I have tried many variations of this query and did some research. I'm not exactly asking for teh codez but a little bit of guidance would be wonderful. I've been stuck at this for a few days while trying to work other parts of the project at the same time.
Any help is much appreciated. Thanks in advance.
[Edit: Added code tags]