php mysql - select most recent data from two tables

Question

I have searched this site and others but cannot find any help with this problem. Maybe I am making this more difficult than necessary.

I have two tables as follows. I want to return all the information for each item but only the most recent data and include data from both tables

table 1: serviceRequestSubmission

sid        customerID
1001     11111
1002     22222
1003     33333
1004     44444
1005     55555

table 2: serviceRequestHistory

historyID     requestID      statusUpdate
1                  1001               Assigned
2                  1002               Assigned
3                  1003               Pending
4                  1004               Delayed
5                  1002               Pending
6                  1002               Closed

I want to return:

sidID           historyID      statusUpdate
1001            1                   Assigned
1002            6                   Closed
1003            3                   Pending
1004            4                   Delayed
1005            -                    Submitted

I have tried the following code but it returns all the rows of data but I only want the most recent/highest historyID.

SELECT serviceRequestSubmissions.*, serviceRequestHistory.* 
FROM serviceRequestSubmissions 
LEFT JOIN serviceRequestHistory ON serviceRequestSubmissions.sid = serviceRequestHistory.requestID 
ORDER BY serviceRequestSubmissions.sid DESC

Any help would be appreciated. Thank you!

Answer 1

Add a LIMIT clause at the end of the query:

LIMIT 2

Answer 2

You can use TIMESTAMP or id in your database and fetch record which has greatest TIMESTAMP or id value or with descending TIMESTAMP or id order by LIMIT clause

Answer 3

This is a common problem of type greatest-n-per-group. I think this is the best way to solve it:

SELECT
    s.sid sidID,
    IFNULL(h1.historyID,'-') historyID,
    IFNULL(h1.statusUpdate,'Submitted') statusUpdate
FROM
    serviceRequestSubmissions s
    LEFT JOIN serviceRequestHistory h1 ON ( s.sid = h1.requestID )
    LEFT JOIN serviceRequestHistory h2 ON ( s.sid = h2.requestID AND ( h1.historyID < h2.historyID OR h1.historyID = h2.historyID AND h1.requestID < h2.requestID ) )
WHERE
    h2.requestID IS NULL
ORDER BY
    s.sid

This is how it works: given a row of the table serviceRequestHistory, there shouldn't be any other row with the same requestID and a greater historyID (the conditions after the OR is to solve the ties). This kind of solution is usually better than using sub-selects.

Answer 4

SELECT serviceRequestSubmissions.*, serviceRequestHistory.* 
FROM serviceRequestSubmissions 
LEFT JOIN serviceRequestHistory 
    ON serviceRequestSubmissions.sid = serviceRequestHistory.requestID
    AND historyID IN (
        SELECT max(historyID) 
        FROM serviceRequestHistory
        GROUP BY requestID
    )
ORDER BY serviceRequestSubmissions.sid DESC

EDIT:

Only closed:

SELECT serviceRequestSubmissions.*, serviceRequestHistory.* 
FROM serviceRequestSubmissions 
JOIN serviceRequestHistory 
    ON serviceRequestSubmissions.sid = serviceRequestHistory.requestID
    AND historyID IN (
        SELECT max(historyID) 
        FROM serviceRequestHistory
        WHERE statusUpdate = 'Closed'
        GROUP BY requestID
    )
ORDER BY serviceRequestSubmissions.sid DESC