Topic: More MySQL Help

[Rob]

OK, let's say I have a table of patients, and a table of illnesses that those patients have suffered from like so...

Code: [Select]

Table 1

Patient ID (PK)                 Patient Name
=============================================
001                             Arthur
002                             Brian
003                             Candy


Table 2

ID (PK)           Patient ID            Illness
==============================================
1                001                  Flu
2                001                  Sore Throat
3                001                  Measles
4                002                  Mumps
5                002                  Flu
6                003                  Cold
7                003                  Measles

Now I want to create a query so that I have a list of each patient with their most recent illness, like

Code: [Select]

Arthur        Measles
Brian          Flu
Candy        Measles


How do I do that? I think it would be like the following (pseudo code)

SELECT * FROM table1, table2 LEFT JOIN USING 'Patient ID' WHERE (and this is where I get stuck) the Illness ID for a particular patient is the biggest.

Thanks for any help!

[Perspective]

I don't see any Illness IDs in your example, but assuming they exist in table2 your query could look something like

SELECT t1.name, t2.illness
FROM table1 t1, table2 t2
WHERE t1.patientID = t2.patientID
AND t2.illnessID IN (SELECT MAX(illnessID) FROM table2 inner WHERE inner.patientID = t1.patientID)


Probably you have a table 3 with the illness ids mapping to names, the query generalizes to a three-way join in the same way.

[Rob]

Thanks! I think I need to go away and digest that.

[Rob]

I may have got confused with your answer (I don't understand where inner.patientID came from) but this only seemed to give me the equivalent of

Code: [Select]

Candy   Measles
That is, just the largest ID, and not the largest ID for each patient.

 

[Rob]

OK, this seemed to work for me...

Code: [Select]

select * from 'table 1', 'table 2' where table_1.patient_ID=table_2.patient_ID and table_2.ID in (select max(ID) from events group by patient.ID)

[Perspective]

>> select max(ID) from events group by patient.ID

This will produce a table of the max for each patient, a patient will then qualify if their illness is in that table i.e., if their illness is anybody's max, not necessarily their own max.

So you could get:
Rob   measles
Rob   flu

If measles is Rob's max, and flu is the max of some other person. You need to qualify the inner query with the patient ID from the outer query.

[Rob]

Isn't that what the

Code: [Select]

where table_1.patient_ID=table_2.patient_ID

does?

[Perspective]

Isn't that what the

Code: [Select]

where table_1.patient_ID=table_2.patient_ID

does?

That doesn't have any effect on the rows generated by the nested query. That query is completely independent, like a separate copy of the same table. You need a WHERE clause in the nested query to make sure the IDs are the same.