CODEWITHSUNDEEP
mysqlsqldatabase
user2048994
user2048994

Reputation: 270

How to write correct query to find students not currently in a course

I am trying to find remaining students which are available to enrol into a course. To do this I need to find students who are currently active and who are not currently in the course. Bu I think the query I am writing is incorrect because it keeps displaying an empty set of results when I run the query how can I find the remaining students who ar able to enrol in the course?

Below is current query:

SELECT  st.StudentId, StudentAlias, StudentForename, StudentSurname
FROM    Course c
        INNER JOIN Student_Course sc
            ON c.CourseId = sc.CourseId
        INNER JOIN Student st
            ON sc.StudentId = st.StudentId
WHERE   (c.CourseId = 1 AND
        sc.StudentId IS NULL
        AND st.Active = 1)
ORDER   BY st.StudentAlias

Below are the tables:

Student: (List of all Students)

StudentId  StudentAlias  StudentForename  StudentSurname  Active
1          u09382        James            Smith           1
2          u83923        John             Brooks          1
3          u38292        Karen            Bradshaw        0
4          u20039        Chris            Cameron         1
5          u39399        Jane             Fields          1

Course: (List of all Courses)

CourseId  CourseNo  CourseName
1         INFO121   ICT
2         BUS122    Business and Finance
3         ENG432    English Language

Student_Course: (List of Students enrolled in respective courses)

StudentId  CourseId
1          1
1          3
2          1
4          2
5          2

So for example if I select CourseId = 1, then it should display students who are active but yet obviously not in CourseId = 1 which means it it should display these students below for possibility of enrollment into course: 

4 - Chris Cameron
5 - Jane Fields

Upvotes: 0

Views: 3861

Answers (3)

Kickstart
Kickstart

Reputation: 21513

To find active students not on a course then something like this:-

SELECT  st.StudentId, StudentAlias, StudentForename, StudentSurname
FROM    Student st
LEFT OUTER JOIN Student_Course sc ON sc.StudentId = st.StudentId
LEFT OUTER JOIN Course c ON c.CourseId = sc.CourseId
WHERE   c.CourseId IS NULL
AND st.Active = 1
ORDER   BY st.StudentAlias

If you want to check students not on a particular course then

SELECT  st.StudentId, StudentAlias, StudentForename, StudentSurname
FROM    Student st
LEFT OUTER JOIN Student_Course sc ON sc.StudentId = st.StudentId
LEFT OUTER JOIN Course c ON c.CourseId = sc.CourseId AND CourseId = 1
WHERE   c.CourseId IS NULL
AND st.Active = 1
ORDER   BY st.StudentAlias

Put the course to check in the ON clause

Upvotes: 0

Meherzad
Meherzad

Reputation: 8553

Try this query 

SELECT s.studentId, s.Forename
FROM Student s
WHERE s.studentId NOT IN (SELECT studentId 
FROM  Student_Course c WHERE c.courseId = 1) AND s.Active = 1

The inner subquery return student of courseId =1 and in outer query we filter those students.

Upvotes: 2

Sachin
Sachin

Reputation: 40970

Try this

SELECT  st.StudentId, StudentAlias, StudentForename, StudentSurname
FROM    Course c
        INNER JOIN Student_Course sc
            ON c.CourseId = sc.CourseId
        INNER JOIN Student st
            ON sc.StudentId = st.StudentId
WHERE   (sc.CourseId <> 1
        AND st.Active = 1)
ORDER   BY st.StudentAlias

Upvotes: 1

Related Questions