• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 776
  • Last Modified:

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

0
Psychotext
Asked:
Psychotext
  • 2
  • 2
  • 2
  • +1
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
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 )
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Slight correction

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.Details_ID = table2.Details_ID )

0
 
PsychotextAuthor Commented:
Unfortunately that didn't appear to work.  I'm still getting back all of the values.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
pssandhuCommented:
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?
0
 
Daniel WilsonCommented:
Can you provide the entire table structure?  The code above should work for your cut-down example.
0
 
pssandhuCommented:
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.
0
 
PsychotextAuthor Commented:
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.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now