如果你想要看过外科医生的病人，你可以使用 exists :

select p.*
from patients p
where exists (select 1
              from visits v join
                   doctors d
                   on d.id_doctor = v.id_doctor_pk
              where v.id_patient_fk = p.id_patient and
                    d.specialty = 'surgeon'
             );

我为你的问题举了个例子。我希望这对你有帮助：

CREATE TABLE #patients2 (
  id_patient int,
  [name] VARCHAR(100),
  last_name VARCHAR(100)
);
CREATE TABLE #doctors2 (
  id_doctor int,
  [name] VARCHAR(100),
  last_name VARCHAR(100),
  speciality VARCHAR(100),
);
CREATE TABLE #dvisits2 (
  id_visit int,
  id_patient_fk  int,
  id_doctor_fk  int
);
  --drop table #table1

INSERT INTO #patients2
  (id_patient, [name], last_name)
VALUES
  (1,'patient1','last_name1'),
  (2,'patient2','last_name2'),
  (3,'patient3','last_name3'),
  (4,'patient4','last_name4'),
  (5,'patient5','last_name5'),
  (6,'patient6','last_name6'),
  (7,'patient7','last_name7'),
  (8,'patient8','last_name8');

  INSERT INTO #doctors2
  (id_doctor, [name], last_name, speciality )
VALUES
  (1,'doctor1','last_name1','surgeon'),
  (2,'doctor2','last_name2','not surgeon'),
  (3,'doctor3','last_name3','surgeon'),
  (4,'doctor4','last_name4','not  surgeon'),
  (5,'doctor5','last_name5','surgeon'),
  (6,'doctor6','last_name6','surgeon'),
  (7,'doctor7','last_name7','not surgeon'),
  (8,'doctor8','last_name8','surgeon');

    INSERT INTO #dvisits2
  (id_visit, id_doctor_fk,id_patient_fk)
VALUES
  (1,1,1),
  (2,2,2),
  (3,3,3),
  (4,4,4),
  (5,5,5),
  (6,6,6),
  (7,7,7),
  (8,8,8);

  select * from #patients2 p 
  join #dvisits2 dv on p.id_patient = dv.id_patient_fk
  join #doctors2 doc on dv.id_doctor_fk = doc.id_doctor
  where doc.speciality = 'surgeon'

结果=

id_patient  [name]      last_name   id_visit    id_patient_fk   id_doctor_fk    speciality  id_doctor   [name]      last_name
1           patient1    last_name1      1               1           1           surgeon         1       doctor1     last_name1
1           patient1    last_name1      1               1           1           surgeon         1       doctor1     last_name1
3           patient3    last_name3      3               3           3           surgeon         3       doctor3     last_name3
3           patient3    last_name3      3               3           3           surgeon         3       doctor3     last_name3
5           patient5    last_name5      5               5           5           surgeon         5       doctor5     last_name5
6           patient6    last_name6      6               6           6           surgeon         6       doctor6     last_name6
8           patient8    last_name8      8               8           8           surgeon         8       doctor8     last_name8