Left Join

[sas1ni69]

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]

[smantscheff]

SELECT Course.courseID,courseName,courseInstructor,if(103 in (select studentID from studentLink s where studentID=103 and s.courseID=Course.courseID),103,NULL) as ID from Course;

There must be a better solution, but I'm too tired. Have a look at the EXISTS clause in select statements.

[sas1ni69]

Thank you so much for your assistance. The code works exactly as it should and I will most certainly look into the EXISTS clause. I already quickly Googled it and it looks close! Thanks again.