Solved

LEFT JOIN to GET ONLY LAST MATCH FROM RIGHT TABLE

Posted on 2008-10-13
4
724 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 60

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

710 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