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.