Left Join

Question

sas1ni69 0 Newbie Poster

13 Years Ago

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

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

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]

mysql sql

Edited 13 Years Ago by sas1ni69 because: n/a

2 Contributors
2 Replies
123 Views
16 Hours Discussion Span
Latest Post 13 Years Ago Latest Post by sas1ni69

smantscheff 265 Veteran Poster

13 Years Ago

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 commented: Solution worked perfectly and even provided a 2nd solution for further research. +0

debasisdas commented: this is why i like your posts +9

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Solution worked perfectly and even provided a 2nd solution for further research.

sas1ni69 0 Newbie Poster · Answer 1 · 2011-04-12T07:34:46+00:00

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.