Show data of Column1 which consists the mentioned values present in column 2

Question

My question is how can I get data of column1 depending on the column2.

This is a small example of data that I have stored.

create table Student_subject
(
    Student varchar(20),
    Subject varchar(20)
)

insert into Student_subject 
values ('Rohan', 'Maths'),
       ('Rohan', 'Science'),
       ('Rohan', 'Language1'),
       ('RAJ'  , 'Maths'),
       ('RAJ'  , 'Science'),
       ('RAJ'  , 'Language1'),
       ('SAM'  , 'Maths'),
       ('SAM'  , 'Language1')

I want to get the distinct row of Students that are studying all three subjects ('Maths', 'Science', 'Language1'). In this case the result will be Rohan and Raj. Please also advise a query on how to achieve the below.

If subjects are specified:

1. List of students that are studying { Maths, Science, Language1 }
2. List of students that are studying { Maths, Science }

Answer 1

SELECT S.STUDENT
FROM Student_subject S
GROUP BY S.Student
HAVING COUNT(DISTINCT S.Subject)=
  (SELECT COUNT (DISTINCT Subject) FROM Student_subject )

Answer 2

You can count distinct subjects for a student.

Select Student
From Student_subject
Group by Student
Having Count(Distinct [Subject])=3

or you can use string_agg function to get a list of target subjects.

Select Student
From Student_subject
Group by Student
Having string_agg([Subject],',') Within Group (Order by [Subject])='Language1,Maths,Science'
Order by Student

Answer 3

try this, maybe it was helpful please consider that if you want to make your query dynamic with multiple inputs you can use json parameter

here is a sample for json input

DECLARE @test NVARCHAR(MAX)='["Language1","Maths"]'
SELECT Student,COUNT(1)
FROM Student_subject
WHERE Subject IN 
(
SELECT Value
FROM OPENJSON(@test,'$')
)
GROUP BY Student
HAVING COUNT(1)>=(SELECT COUNT(Value)
FROM OPENJSON(@test,'$'))

Answer 4

SELECT student FROM student_subject GROUP BY S.Student HAVING COUNT(DISTINCT S.Subject)= (SELECT COUNT (DISTINCT Subject) FROM Student_subject )