asked on

SQL Server - Too many rows returned by join

I have the following tables (cut down examples)

Table1: [Detail_ID], [Information]
Table2: [ID], [Detail_ID], [Date], [MoreInformation]

What I'm trying to do is get the following query:

Table1.[Detail_ID], Table1.[Information], Table2.[Date], Table2.[MoreInformation]

The problem is that Table2 is linked on a one to many basis with Table1, so when I join it I get multiple rows. Taking this as the source data:

Table1

1 : Info1
2 : Info2
3 : Info3

Table2

1 : 1 : 01/01/2000 : MoreInfo1
2 : 1 : 02/03/2001 : MoreInfo2
3 : 2 : 01/01/2000 : MoreInfo3
4 : 2 : 02/03/2001 : MoreInfo4
5 : 3 : 01/03/2002 : MoreInfo5

I would like to return the following (Getting only the most recent data for each ID in Table2:

1 : Info1 : 02/03/2001 : MoreInfo2
2 : Info2 : 02/03/2001 : MoreInfo4
3 : Info3 : 01/03/2002 : MoreInfo5

Unfortunately I can only get it to return all of the rows from Table2 at the moment. Your assistance is appreciated.

Open in new window

Aneesh

SELECT Table1.[Detail_ID], Table1.[Information], Table2.[Date], Table2.[MoreInformation]
FROM Table1 INNER JOIN Table2
ON Table1.Detail_ID = Table2.Detail_Id
WHERE Table2.Date = (SELECT MAX(Date) from Table2 t2 where t2.ID = table2.ID )

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Psychotext

ASKER

Unfortunately that didn't appear to work. I'm still getting back all of the values.

pssandhu

Is your ID column an identity column? In other words each time a new record is inserted does the value in the ID column added automatically?

Daniel Wilson

Can you provide the entire table structure? The code above should work for your cut-down example.

pssandhu

Try this:

Select t1.*, t2.Date, t2.MoreInformation
From Table1 t1 INNER JOIN (Select t.*
From Table2 t INNER JOIN (Select Detail_Id, Max(Date) Date
From Table2
Group by Detail_ID) d
ON t.Detail_id = d.Detail_id and t.Date = d.Date) t2
ON t1.Detail_id = t2.Detail_id

Cheers!

P.

Psychotext

ASKER

My apologies, the suggestion from aneeshattingal was not in error... I've just double checked it and there is however a slightly different issue involving duplicate dates in table 2.