x

Select a specific row from a joined table

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

more ▼

asked Dec 18, 2009 at 12:32 PM in Default

Roger Olofsson gravatar image

Roger Olofsson
1 1 1 1

are you trying to get top 2 entries after the join or is there more to the requirement?

Good effort on showing the data. It'll also help if you show the data (maybe up to 4 entries for this question), and expected result from that data.
Dec 18, 2009 at 02:11 PM Rajib Bahar
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Dec 20, 2009 at 11:53 AM

Dinesh Kumar gravatar image

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, 2009 at 07:38 AM Roger Olofsson
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x376
x51

asked: Dec 18, 2009 at 12:32 PM

Seen: 814 times

Last Updated: Dec 21, 2009 at 10:47 AM