CODEWITHSUNDEEP
mysqlmysql-workbench
onenonly
onenonly

Reputation: 35

mysql - How to limit certain records in a SELECT query

Okay, so I have a relation of students who enrolled in multiple subjects. Most of the students were enrolled in only one year, either 2015 or 2016, but I have one that is enrolled in subjects from 2015 but also had some in 2016. I want to be able to show the students who were enrolled in 2015 only. However, when I do my SELECT statement, I am also grabbing the one student who did the subject in 2016 as well. The subject code includes the year the subject was done in.

Some of the data from the enrolled relation:

enter image description here

Code:

SELECT s.firstName, d.degreeCode
FROM students s, degrees d, enrolled e
WHERE s.studentID = d.studentID AND s.studentID = e.studentID AND e.subjectCode NOT LIKE '_______16__'
GROUP BY firstName;

So I am obtaining all of the students who studied in 15, but I don't want s3 in the result table as he also studied in 2016.

Upvotes: 0

Views: 50

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522751

One approach uses aggregation to assert that a given student's subject code only occurs in a single year.

SELECT s.firstName, d.degreeCode
FROM students s
INNER JOIN degrees d
    ON s.studentID = d.studentID
INNER JOIN ENTROLLED e
    s.studentID = e.studentID
WHERE
    SUBSTRING(e.subjectCode, 8, 2)) = '15' AND
    s.studentID NOT IN (
        SELECT d.studentID
        FROM degrees d
        INNER JOIN enrolled e
            ON d.studentID = e.studentID
        GROUP BY d.studentID
        HAVING COUNT(DISTINCT SUBSTRING(e.subjectCode, 8, 2)) > 1
    );

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133400

You could check for the all the student NOT IN the select for student LIKE '_______16__'

SELECT s.firstName, d.degreeCode
FROM students s  
INNER JOIN degrees d ON s.studentID = d.studentID
INNER JOIN enrolled e  AND s.studentID = e.studentID 
WHERE s.StudentID NOT IN (
    select studentID 
    FROM students s
    INNER JOIN degrees d ON s.studentID = d.studentID
    INNER JOIN enrolled e  AND s.studentID = e.studentID 
    WHERE e.subjectCode NOT LIKE  '%_15_%'

)

Upvotes: 0

Related Questions