Solved

LEFT JOIN to GET ONLY LAST MATCH FROM RIGHT TABLE

Posted on 2008-10-13
4
715 Views
Last Modified: 2013-12-07
I have a need to execute a query that pulls all data from one table regardless of whether matching data (matched by a foreign key) is present in a second table. This would be a classic LEFT JOIN query except that if a matching record is found in the RIGHT table (table2), then I need to pull only the latest such record from that table.

In particular, I need to pull one field, called VE (maybe more in the future) from the RIGHT table, but only from the latest record in the RIGHT table that matches the data in the LEFT table. The basic scenario I have is an object described in the LEFT table and the results of a process performed on that object in the RIGHT table. There may be more than one process result for that object in the right table, but only the latest one matters to me for the purposes of this query.

To solve this problem I used the following query, but it didn't quite work out as planned. The ID in this query is an autonumber field that I'm using to retrieve the latest record.
 
SELECT Details.*, Results.VE
FROM Details LEFT JOIN [SELECT Results.VE, Results.SerialNumber, Max(Results.ID) From Results Group By Results.VE, Results.SerialNumber]. AS Results ON Details.SN = Results.SerialNumber);

This query was limited because there are some instances where the process results (RIGHT Table) has more than one result (same SerialNumber) with different VE values.  This will actually be quite common.  

How, then, do I get the latest Results record for a particular serial number using something like the LEFT JOIN query so that I can bring in the data from the LEFT table even if the RIGHT table has no data for the object in the LEFT table?  

Thanks.  
0
Comment
Question by:l2Bravo
4 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 22707149
Try something to this effect:
SELECT Details.*, r1.VE

FROM Details LEFT JOIN 

(Results r1 INNER JOIN (SELECT SerialNumber, Max(ID) As maxID FROM Results GROUP BY SerialNumber) AS r2

ON r1.SerialNumber = r2.SerialNumber AND r1.ID = r2.maxID)

ON Details.SN = r1.SerialNumber;

Open in new window

0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 22707158
Will this work?
SELECT Details.*, Results.VE

FROM Details LEFT JOIN (SELECT Results.VE, Results.SerialNumber, Max(Results.ID)

                          From Results 

                          Group By Results.VE, Results.SerialNumber

                          having Results.ID = Max(Results.ID)

                       ) AS Results

                       ON Details.SN = Results.SerialNumber

Open in new window

0
 
LVL 27

Expert Comment

by:sujith80
ID: 22710108
try something like this. (correct the syntax, i am not familiar with sql syntax)

SELECT Details.*, finalresults.VE
FROM Details LEFT JOIN
[select VE, serialNumber from
[[SELECT Results.VE, Results.SerialNumber,
        row_number() over(partition by SerialNumber order by id desc) rn
 From Results Group By Results.VE, Results.SerialNumber]. AS Results ]
 where results.rn = 1 ] as finalresults
ON Details.SN = finalresults.SerialNumber);
0
 

Author Closing Comment

by:l2Bravo
ID: 31505703
Thanks.  This solution in its basic elements worked.  I had to correct some omissions, but the basic idea worked and is exactly what I need.  The omissions were in the LEFT JOIN subquery-SELECT r1.* FROM Results and  AS r1 after the subquery, but when I added that bit to the query, along with some other details needed to complete the query as I need it to work, everything worked fine.  

Thanks agin.  
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

867 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

12 Experts available now in Live!

Get 1:1 Help Now