CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
user594166
user594166

Reputation:

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          Spanish

   .....

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.

Upvotes: 1

Views: 478

Answers (3)

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

Try this:

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

Upvotes: 1

Jeff Hornby
Jeff Hornby

Reputation: 13680

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)

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453897

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

Upvotes: 1

Related Questions