Solved

SQL Server - Too many rows returned by join

Posted on 2007-12-04
7
745 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
ID: 20402929
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
ID: 20402932
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
ID: 20403495
Unfortunately that didn't appear to work.  I'm still getting back all of the values.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 17

Expert Comment

by:pssandhu
ID: 20403752
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
ID: 20403762
Can you provide the entire table structure?  The code above should work for your cut-down example.
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 20403886
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
ID: 20404005
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

774 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