How to select rows in a many-to-many relationship? (SQL)

I have a Students table and a Courses table. They have a many to many relationship between them and the StudentCourses table is the intermediary. Now, I have a list of Course ids and want to select the Students that follow all Courses in my list. How??

--CREATE TYPE CourseListType AS TABLE
--(
--  CourseID INT
--)

DECLARE
     @CourseList CourseListType

CREATE TABLE #Students
(
     ID INT
    ,Name CHAR(10)
)

CREATE TABLE #Courses
(
     ID INT
    ,Name CHAR(10)
)

CREATE TABLE #StudentCourses
(
     StudentID INT
    ,CourseID INT
)

INSERT INTO @CourseList (CourseID)
    VALUES
         (1) --English
        ,(2) --Math

INSERT INTO #Students (ID, Name)
VALUES
     (1, 'John')
    ,(2, 'Jane')
    ,(3, 'Donald')

INSERT INTO #Courses (ID, Name)
VALUES
     (1, 'English')
    ,(2, 'Math')
    ,(3, 'Geography')

INSERT INTO #StudentCourses (StudentID, CourseID)
VALUES
     (1, 1)
    ,(1, 2)
    ,(2, 1)
    ,(2, 2)
    ,(3, 1)
    ,(3, 3)

In this example, I only want the result to be John and Jane, because they both have the two courses in my CourseList. I dont want Donald, because he only has one of them.

Have tried this JOIN, construction, but it does not eliminate students that only have some of my desired courses.

SELECT 
    * 
FROM 
    @CourseList CRL
    INNER JOIN #Courses CRS ON CRS.ID = CRL.CourseID
    INNER JOIN #StudentCourses STC ON STC.CourseID = CRS.ID
    INNER JOIN #Students STD ON STD.ID = STC.StudentID

Answer

If you want students with all your required courses, you can use aggregation and having:

SELECT sc.StudentId 
FROM #StudentCourses sc JOIN
    @CourseList cl
    ON sc.CourseID = cl.id
GROUP BY sc.StudentId
HAVING COUNT(DISTINCT sc.CourseId) = (SELECT COUNT(*) FROM @DcourseList);

If you want additional information about students, you can join in the Students table (or use a IN or a similar construct).

Note that this only needs the StudentCourses table. It has the matching ids. There is no need to join in the reference tables.

Advertisement

Answer