sql foreach without using cursor

Question

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.

Dumitrescu Bogdan · Accepted Answer · 2011-03-24 16:09:56Z

1

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)

edited Mar 24, 2011 at 16:09

answered Mar 24, 2011 at 15:55

Dumitrescu Bogdan

7,2652 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lamak Over a year ago

+1, But you are missing the alias of the table for the SELECT on StudentId

Martin Smith · Accepted Answer · 2011-03-24 15:48:21Z

1

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

answered Mar 24, 2011 at 15:48

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Comments

Jeff Hornby · Accepted Answer · 2011-03-24 15:55:07Z

1

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)

answered Mar 24, 2011 at 15:55

Jeff Hornby

13.8k4 gold badges42 silver badges65 bronze badges

1 Comment

user594166 Over a year ago

Thanks Jeff.Combining Group by join and the trick Count() = (SELECT COUNT() FROM @Language) gives the right result.

Collectives™ on Stack Overflow

sql foreach without using cursor

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related