CODEWITHSUNDEEP
mysqlsqlforeign-keys
Zeleska
Zeleska

Reputation: 107

How to retrieve data solely by foreign key in MySQL?

The mission: Visualize the patients that were treated by surgeons.

Table: patients

Table: doctors

Table: visits

With the id_doctor_fk pointing to id_doctor, I have to see if that doctor is a surgeon, right? Is this possible? I gave up, so I ask.

Upvotes: 0

Views: 237

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

If you want patients who have seen a surgeon, you can use 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'
             );

Upvotes: 1

hussein zakizadeh
hussein zakizadeh

Reputation: 173

i created an example for your question . i hope this is help you:

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'

result =

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

Upvotes: 1

Related Questions