Select a specific row from a joined table

Question

Hi,

I'm using MS Access 2003 and I have two tables, tablePatients and tableTreatments where tablePatients contains a specific patient and any of these patients may have one or more treatments which is linked to tableTreatments.

Eg:

tablePatients                    
                    
ID NAME   DISEASE                    
1  Alex   Cancer                    
2  Duke   Cancer                    
3  Lex    Cancer                    
                    
tableTreatments                    
                    
ID TREATMENT DATE(YY/MM/DD)                    
1  Surgery   01/01/12                    
1  Chemo     02/02/14                    
1  Surgery   04/08/14                    
2  Chemo     01/12/12                    
2  Surgery   03/11/13                    
3  Chemo     01/06/08

So far I've used this question to retrieve all patients and their treatment:

SELECT *                     
FROM TablePatients                     
INNER JOIN TableTreatments ON TablePatients.ID= TableTreatment.ID;

But now I would like to retrieve only the second (or third, or fourth or X..) treatment for all the patients based on the treatment date, so using the example above the data I would like to retrieve is:

ID NAME DISEASE TREATMENT DATE                    
                    
1  Alex Cancer  Chemo     02/02/14                    
2  Duke Cancer  Surgery   03/11/13

Is there anyone having an idea?

Merry christmas,

Roger

Answer 1

0

Hi,

You may use the below query.

SELECT tablePatients.ID, tablePatients.Name,             
tablePatients.DISEASE, tableTreatments.TREATMENT,             
tableTreatments.DATE            
FROM (tablePatients INNER JOIN tableTreatments             
ON tablePatients.ID = tableTreatments.ID)             
INNER JOIN (SELECT Max(tableTreatments.DATE) AS MaxOfDATE,             
tableTreatments.ID FROM tableTreatments             
GROUP BY tableTreatments.Id )MaxDate            
ON (tableTreatments.DATE = MaxDate.MaxOfDATE)             
AND (tablePatients.ID = MaxDate.ID);

hope this would help

answered Dec 20 '09 at 11:53 AM

Dinesh Kumar
11 ● 2 ● 2 ● 3

Hi Dinesh,

a big thanks for your comment! The solution works as such that it delivers only the latest treatments. But what I want is a solution where I can specify which treatment in order that I want to retrive. For example if I want to retrieve the details for the second treatment for all patients (or the third, or the forth)?

How do I do that?

Dec 21 '09 at 07:38 AM Roger Olofsson

(comments are locked)

Select a specific row from a joined table

Follow this question

Related Questions