CODEWITHSUNDEEP
sqloracle-databasesqlplus
kpAtCh
kpAtCh

Reputation: 107

SQL - Filtering from course then subjects

I need help with this problem:

Query showing the student ID, name and program of all CIS or UT students who took all three of the courses COSC121, COSC126 and MATH111. Order the results by student ID. Hint: Consider using a self-join.

Here are the tables:

Students Student_ID Stu_name Program

Grades Student_ID Course_ID Grade

Here's what I have so far.

SELECT s.student_id, s.stu_name, s.program
FROM students s, grades g
WHERE s.student_id = g.student_id AND
(program = 'CIS' OR program = 'UT') AND
course_id = 'COSC126'
ORDER BY student_id;

If I change the line course_id = 'COSC126' to (course_id = 'COSC121' AND course_id = 'COSC126' AND course_id = 'MATH111'), I'm not getting any result.. thank you in advance!

Upvotes: 0

Views: 159

Answers (2)

Ed Bangga
Ed Bangga

Reputation: 13006

Here's your query

select s.student_id, s.stu_name, s.program
from students s
inner join grades g on s.student_id = g.student_id 
inner join students t1 on t1.student_id = s.student_id and t1.course_id = 'COSC121'
inner join students t2 on t2.student_id = s.student_id and t2.course_id = 'COSC126'
inner join students t3 on t3.student_id = s.student_id and t3.course_id = 'MATH111'
where 
 s.program in ('CIS', 'UT') or t1.program in ('CIS', 'UT') or t2.program in ('CIS', 'UT')
or t3.program in ('CIS', 'UT')

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

Taking the self-join hint we can try:

SELECT
    s1.student_id,
    s1.stu_name,
    s1.program
FROM students s1
INNER JOIN students s2
    ON s1.student_id = s2.student_id
INNER JOIN students s3
    ON s1.student_id = s3.student_id
WHERE
    s1.program IN ('CIS', 'UT') AND
    s1.course_id = 'COSC121' AND
    s2.course_id = 'COSC126' AND
    s3.course_id = 'MATH111';

I usually use an aggregation approach for this type of problem though:

SELECT
    student_id,
    stu_name,
    program
FROM students
WHERE
    program IN ('CIS', 'UT') AND
    course_id IN ('COSC121', 'COSC126', 'MATH111')
GROUP BY
    student_id,
    stu_name,
    program
HAVING
    COUNT(DISTINCT course_id) = 3;

Upvotes: 1

Related Questions