Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 738
  • Last Modified:

LEFT JOIN to GET ONLY LAST MATCH FROM RIGHT TABLE

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
l2Bravo
Asked:
l2Bravo
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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
 
Terry WoodsIT GuruCommented:
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
 
SujithData ArchitectCommented:
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
 
l2BravoAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now