Solved

LEFT JOIN to GET ONLY LAST MATCH FROM RIGHT TABLE

Posted on 2008-10-13
4
718 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

808 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