Solved

SQL Server - Too many rows returned by join

Posted on 2007-12-04
7
721 Views
Last Modified: 2008-02-01
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
Comment
Question by:Psychotext
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
Comment Utility
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
 
LVL 2

Author Comment

by:Psychotext
Comment Utility
Unfortunately that didn't appear to work.  I'm still getting back all of the values.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
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
 
LVL 32

Expert Comment

by:Daniel Wilson
Comment Utility
Can you provide the entire table structure?  The code above should work for your cut-down example.
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
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
 
LVL 2

Author Comment

by:Psychotext
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now