It's my job to read other threads which is my first question here. Here goes

I am attempting to develop a query which involves two tables, the course table and also the studentsLink table. The StudentsLink table describes the hyperlink between students and also 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 preferred result's as below given basically was searching 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 which i have to date is really 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 am essentially looking to get an effect group of out all of the courses available, which may be the particular student registered in and which is he not too I'll have the ability to display it as being a training course which we are able to offer towards the student.

I've attempted many versions of the query and did some investigation. I am not quite requesting teh codez but some guidance could be wonderful. I have been stuck only at that for any couple of days while attempting to work other areas from the project simultaneously.

Any assistance is much appreciated. Thanks ahead of time.

SELECT ID, CourseName, CourseInstructor, StudentId, CourseId 
FROM Courses as c 
LEFT JOIN StudentLink as sl ON c.id = sl.CourseId  And StudentId = 103 

The issue here's that you are joining then blocking, you have to filter at the purpose of the join

SELECT * 
FROM course
LEFT JOIN studentLink 
ON course.courseID = studentLink.courseID and studentLink.studentID = 103
ORDER BY course.courseID DESC

This will work (presuming mysql allows you've multiple predicates around the join logic, think it will try not to come with an instance to check on)

Failing that you could join to some subquery that is applicable the restriction for you personally.

SELECT * 
FROM course
LEFT JOIN 
(select * from studentLink where studentID = 103) as sl
ON course.courseID = sl.courseID
ORDER BY course.courseID DESC

It is simple to get those that a student is within making use of your left join (w/o the or perhaps in the where)

After that you can obtain the others where utilizing a not in as part of a union...

after your frist area of the query you could do this something similar to...

SELECT *  FROM course LEFT JOIN studentLink  ON course.courseID = studentLink.courseID WHERE studentLink.studentID = 103

union

select * FROM course LEFT JOIN studentLink  ON course.courseID = studentLink.courseID 
WHERE courseID NOT IN ( select course.courseID FROM course LEFT JOIN studentLink  ON course.courseID = studentLink.courseID WHERE studentLink.studentID = 103)

Which will most likely try taking some fine-tuning, i am unsure from the exact syntax, but it is a potential concept of how you can obtain the thing you need.