CODEWITHSUNDEEP
mysqlsqldatabaseleft-join
Hass
Hass

Reputation: 1636

Mysql Help - Left join

I usually read other threads and this is my first question here. Here goes;

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.

Upvotes: 2

Views: 134

Answers (3)

Patrick
Patrick

Reputation: 7722

You can easily get the ones that the student is in using your left join (w/o the or in the where)

You can then get the others where using a not in as a part of a union...

after your frist part of the query you could do something like...

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)

That will probably take some tweaking, i'm not sure of the exact syntax, but it's a possible idea of how to obtain what you need.

Upvotes: 0

Robb
Robb

Reputation: 3851

The problem here is that you're joining then filtering, you need to filter at the point of the join

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

This should work (assuming mysql lets you have multiple predicates on the join logic, think it does but don't have an instance to test on)

Failing that you can join to a subquery that applies the restriction for you.

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

Upvotes: 1

HLGEM
HLGEM

Reputation: 96650

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

Upvotes: 1

Related Questions