Get Filtered columns from a table in SQL

Question

I have a tabl1 with two columns (professors and the subjects they teach) and other table2 . Now I am trying to find out all the professors who can teach all the subjects in table2. I tried Join and Group by operations and got it successfully. But I heard from my friends that we can solve this problem even more efficiently using divide operator. I am trying from past two days, no luck. can some one help me in how to do that in any other way or any clue is appreciated.

Eg: table1

Prof  Subject
A     S1
A     S4
B     S1
B     S3
C     S1
C     S2
C     S3

table2

S1
S3

desired output:

C,B

Answer 1

SELECT
    Prof
FROM Table1
INNER JOIN Table2
    ON [Table1].[Subject] = [Table2].[Subject]
GROUP BY [Table1].Prof
HAVING COUNT(*) (SELECT COUNT(*) FROM Table2)

This will list all off the professors that have all of the entries. If you need the results on the same line, then concatenate the values together.

DECLARE @Result VARCHAR(MAX) = ''
SELECT @Result = @Result + ',' + Prof
FROM Table1
INNER JOIN Table2
    ON [Table1].[Subject] = [Table2].[Subject]
GROUP BY [Table1].Prof
HAVING COUNT(*) = (SELECT COUNT(*) FROM Table2)

SELECT STUFF(@Result,1,1,'')

Answer 2

Can you share the tables structure so we have the big picture more clearly ?

You'll maybe find this helpful : Example of Join,

Table1 (SubjectId, SubjectLabel)

Table2 (TeacherId, TeacherName, SubjectId)

SELECT TeacherName, SubjectLabel FROM Table1, Table2 WHERE Table1.SubjectId = Table2.SubjectId