Solved

SQL Server - Too many rows returned by join

Posted on 2007-12-04
7
734 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

932 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

13 Experts available now in Live!

Get 1:1 Help Now