Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 2 linked tables:

patients: (one)
ID number last first
1 1001 doe john
2 1324 big mike

procedures: (many)
ID date procedure
1 1/1/01 appendectomy
1 1/1/03 colonoscopy
1 1/1/04 CT scan
2 1/1/01 prostatectomy
2 1/3/04 rhionoplasty

I want to find all patients who have had BOTH prostatectomies and rhioplasty:
query:
2 1324 big mike

How do I do this?
 
I have 2 linked tables:

patients: (one)
ID number last first
1 1001 doe john
2 1324 big mike

procedures: (many)
ID date procedure
1 1/1/01 appendectomy
1 1/1/03 colonoscopy
1 1/1/04 CT scan
2 1/1/01 prostatectomy
2 1/3/04 rhionoplasty

I want to find all patients who have had BOTH prostatectomies and rhioplasty:
query:
2 1324 big mike

How do I do this?
Click to expand...

You'll need to create a query joining Patients to Procedures... *twice*. Add
the Patients table to the query grid, and add two copies of Procedures; join
both by ID, if the join line isn't created automatically (it probably won't be
for the second instance). Select the Procedure name from each instance, and
use "Prostatectomy" as a criterion on one, and "rhinoplasty" for the other...

ahd give Big Mike my sympathy for "burning the candle at both ends"... ouch!

John W. Vinson [MVP]
 
John W. Vinson said:
You'll need to create a query joining Patients to Procedures... *twice*. Add
the Patients table to the query grid, and add two copies of Procedures; join
both by ID, if the join line isn't created automatically (it probably won't be
for the second instance). Select the Procedure name from each instance, and
use "Prostatectomy" as a criterion on one, and "rhinoplasty" for the other...

ahd give Big Mike my sympathy for "burning the candle at both ends"... ouch!

John W. Vinson [MVP]
Click to expand...

Wise one:

1) What did the "join" operation do that allowed me to "AND" the criteria?
2) The results show the patient ID for every instance of the second
procedure in the query. In other words, big mike had multiple rhinoplasties
(a function of going to a surgeon featured on Doctor 90210), and thus his
name is listed multiple times in the query results. Is there any workaround?
3) Is there a good reference that I could consult that would allow me to
figure these things out without having to pester you?

BTW, big mike has torsonic polarity syndrome, and therefore the rhinoplasty
and prostatectomy were performed on the same orifice.

--TK
 
3) Is there a good reference that I could consult that would allow me to
figure these things out without having to pester you?
Click to expand...

Reference. SQL Queries for Mere Mortals, Michael J. Hernandez and John L.
Viescas

p. 365

SELECT patients.*
FROM patients
WHERE EXISTS
(SELECT *
FROM patientprocedures
INNER JOIN procedures
ON patientprocedures.procedure_id = procedures.procedure_id
WHERE procedures.procedure_name = "colonoscopy"
AND patients.patient_id = patientprocedures.patient_id)
AND EXISTS
(SELECT *
FROM patientprocedures
INNER JOIN procedures
ON patientprocedures.procedure_id = procedures.procedure_id
WHERE procedures.procedure_name = "prostatectomy"
AND patients.patient_id = patientprocedures.patient_id);
 
Reference. SQL Queries for Mere Mortals, Michael J. Hernandez and John L.
Viescas

p. 365

SELECT patients.*
FROM patients
WHERE EXISTS
(SELECT *
FROM patientprocedures
INNER JOIN procedures
ON patientprocedures.procedure_id = procedures.procedure_id
WHERE procedures.procedure_name = "colonoscopy"
AND patients.patient_id = patientprocedures.patient_id)
AND EXISTS
(SELECT *
FROM patientprocedures
INNER JOIN procedures
ON patientprocedures.procedure_id = procedures.procedure_id
WHERE procedures.procedure_name = "prostatectomy"
AND patients.patient_id = patientprocedures.patient_id);
Click to expand...

Elegant! Just as one would expect from my esteemed colleague, the "first" John
V.


John W. Vinson [MVP]
 
1) What did the "join" operation do that allowed me to "AND" the criteria?
Click to expand...

It let you include the Procedure field from two different records of the
Procedures table, joined to the one Patient record.
2) The results show the patient ID for every instance of the second
procedure in the query. In other words, big mike had multiple rhinoplasties
(a function of going to a surgeon featured on Doctor 90210), and thus his
name is listed multiple times in the query results. Is there any workaround?
3) Is there a good reference that I could consult that would allow me to
figure these things out without having to pester you?
Click to expand...

I see Michael has given you good answers to these two.
BTW, big mike has torsonic polarity syndrome, and therefore the rhinoplasty
and prostatectomy were performed on the same orifice.
Click to expand...

Head wedged, eh? <g>

See: http://www.docrat.com.au/default.asp?id=strip&thisitem=8


John W. Vinson [MVP]
 
Back
Top