SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table student (id int, patient varchar(20), group_c varchar(20))
insert into student values(1, 'jhon', 'group1');
1 rows affected

insert into student values(2, 'jack', 'group1');
1 rows affected

insert into student values(3, 'jill', 'group2');
1 rows affected

insert into student values(4, 'jack', 'group1');
1 rows affected

insert into student values(5, 'jill', 'group1');
1 rows affected

insert into student values(6, 'peter', 'group2');
1 rows affected

insert into student values(7, 'peter', 'group3');
1 rows affected

select * 
from student 
where patient in (
select patient
from student
where group_c in ('group1', 'group2')
group by patient
having count(distinct group_c) = 2)


id
patient
group_c


3
jill
group2

5
jill
group1