Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TK - 21 May 2007 05:25 GMT

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?

Reply to this Message

John W. Vinson - 21 May 2007 06:31 GMT

>I have 2 linked tables:
>
[quoted text clipped - 16 lines]
>
>How do I do this?

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]

Reply to this Message

TK - 21 May 2007 14:10 GMT

> >I have 2 linked tables:
> >
[quoted text clipped - 26 lines]
>
> John W. Vinson [MVP]

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

Reply to this Message

Michael Gramelspacher - 21 May 2007 16:05 GMT

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

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);

Reply to this Message

John W. Vinson - 21 May 2007 17:55 GMT

>> 3) Is there a good reference that I could consult that would allow me to
>> figure these things out without having to pester you?
[quoted text clipped - 20 lines]
> WHERE procedures.procedure_name = "prostatectomy"
> AND patients.patient_id = patientprocedures.patient_id);

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

John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 22 May 2007 05:41 GMT

>1) What did the "join" operation do that allowed me to "AND" the criteria?

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?

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.

Head wedged, eh? <g>

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

John W. Vinson [MVP]

Reply to this Message

TK - 22 May 2007 23:58 GMT

> >1) What did the "join" operation do that allowed me to "AND" the criteria?
>
[quoted text clipped - 18 lines]
>
> John W. Vinson [MVP]

Thanks. Both responses have been a great help!

Reply to this Message