Solved

LEFT JOIN to GET ONLY LAST MATCH FROM RIGHT TABLE

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

828 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