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 }

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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,'$'))

CodePudding user response：

SELECT student FROM student_subject