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)

​x
 
--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

 
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);

 
SELECT sc.StudentId FROM #StudentCourses sc JOIN    @CourseList cl    ON sc.CourseID = cl.idGROUP BY sc.StudentIdHAVING 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