sql foreach without using cursor

I have a table "Student" in sql with a structure:

   StudentId   FirstName LastName

    1            X         Y

    ....

AND a table of Languages ,its structure:

  LanguageId   Name
    1          English
    2          Mandarin
    3          S开发者_JAVA技巧panish

   .....

and a relationship table StudentLanguage (languages spoken by a student)

  StudentId            LanguageId

    1                    1
    1                    3

    2                    1
    2                    2

from my asp.net page i want to filter students by spoken languages using checkboxes.

for example,when i check English,Madarin i want to have students speaking both English and Madarin When i check French,Spanish ,English ==>Get students speaking French,AND English,AND Spanish.

to do that i pass a table of Languages paramter called @LanguageTable(LanguageId smallint) to a stored procedure. how can i use this table to get the students without using a cursor.

I have tried with CTE but no result.

Try this:

select s.StudentId,s.FirstName,s.LastName
from Stundent s
join StudentLanguage sl on s.StudendID=sl.StudentId
join @LanguageTable on sl.LanguageId=@LanguageTable.LanguageId
group by s.StudentId,s.FirstName,s.LastName
having count(*)=(select count(*) from @LanguageTable)

You need relational division.

SELECT s.StudentId, s.FirstName, s.LastName
FROM   Student s
WHERE  NOT EXISTS(SELECT *
                  FROM   @LanguageTable l
                  WHERE  NOT EXISTS(SELECT *
                                    FROM   StudentLanguage sl
                                    WHERE  sl.LanguageId = l.LanguageId
                                           AND sl.StudentId = s.StudentId))  

I haven't tested this but:

SELECT s.StudentId, s.FirstName, s.LastName
FROM Student s
    INNER JOIN StudentLanguage sl ON sl.StudentId = s.StudentId
    INNER JOIN @Language l ON l.LanguageId = sl.LanguageId
GROUP BY s.StudentId, s.FirstName, s.LastName
HAVING Count(*) = (SELECT COUNT(*) FROM @Language)