limit sub-query result set to last record

Question

I have four tables that are linked. The itemStatus table can have multiple records per Cart item to show its history (e.g. purchased, processed, shipped, delivered). The Shop table pulls in the Cart items name. The Order table defines all the Cart items in the order.

To show the latest status (not entire history) of each Cart item in the Order, I need to only obtain the last record for itemStatus. I am using a sub-query to obtain the records of the itemStatus table, but can not figure out how to get the last record. Playing with the below code, I end up always getting the first record:

SELECT
    tblOrders.id,tblOrders.status as orderStatus,tblOrders.created,tblCart.id AS cartID,tblCart.status AS cartStatus,tblCart.qty,tblCart.price,tblShop.title,tblItem.itemStatus 
FROM
    ".PREFIX."Orders tblOrders 
LEFT JOIN
    ".PREFIX."Cart tblCart ON tblOrders.id=tblCart.OID 
LEFT JOIN
    ".PREFIX."Shop tblShop ON tblCart.PID=tblShop.id 
LEFT JOIN
    (SELECT
        CID,status as itemStatus
    FROM
        ".PREFIX."ItemStatus
    WHERE
        status IN ('pending','refunded','cancelled','purchased','backordered','shipping','delivered')
GROUP BY CID
    ORDER BY
        created DESC
    ) tblItem ON tblCart.id=tblItem.CID 
WHERE
    tblOrders.status<>'disabled' AND tblCart.status='purchased' AND tblCart.BID='".$gbl_user['id']."' 
ORDER BY
    tblOrders.updated DESC

As shown above, I have tried using the 'GROUP BY' clause to limit the number of records returned by the sub-query, but that does not produce desired results. I have also tried placing a 'LIMIT 1' clause in the sub-query too, but that only limits the number of records returned to 1 (so any other cart items do not end up with a status). Any help would be appreciated!

UPDATE:

Per the duplication marking, I attempted to update the code using the MAX() statement and removal of 'ORDER BY' as follows:

SELECT
    tblOrders.id,tblOrders.status as orderStatus,tblOrders.created,tblCart.id AS cartID,tblCart.status AS cartStatus,tblCart.qty,tblCart.price,tblShop.title,tblItem.itemStatus 
FROM
    ".PREFIX."Orders tblOrders 
LEFT JOIN
    ".PREFIX."Cart tblCart ON tblOrders.id=tblCart.OID 
LEFT JOIN
    ".PREFIX."Shop tblShop ON tblCart.PID=tblShop.id 
LEFT JOIN
    (SELECT
        CID,status as itemStatus,MAX(created) as itemCreated
    FROM
        ".PREFIX."ItemStatus
    WHERE
        status IN ('pending','refunded','cancelled','purchased','backordered','shipping','delivered')
GROUP BY CID
    ) tblItem ON tblCart.id=tblItem.CID 
WHERE
    tblOrders.status<>'disabled' AND tblCart.status='purchased' AND tblCart.BID='".$gbl_user['id']."' 
ORDER BY
    tblOrders.updated DESC

UPDATE 2:

I had provided the actual code that works to solve the problem, but it wasn't approved for some reason. I am posting the correct code below:

SELECT
    tblOrders.id, tblOrders.status as orderStatus, tblOrders.created,
    tblCart.id AS cartID, tblCart.status AS cartStatus,tblCart.qty,
    tblCart.price, tblShop.title, tblItem.status as itemStatus 
FROM
    ".PREFIX."Orders tblOrders 
LEFT JOIN
    ".PREFIX."Cart tblCart ON tblOrders.id=tblCart.OID 
LEFT JOIN
    ".PREFIX."Shop tblShop ON tblCart.PID=tblShop.id 
LEFT JOIN
    ".PREFIX."ItemStatus tblItem ON tblCart.id=tblItem.CID 
JOIN (
    SELECT
        CID, MAX(created) AS maxCreated
    FROM
        ".PREFIX."ItemStatus
    WHERE
        status IN ('pending','refunded','cancelled','purchased','backordered','shipping','delivered')
    GROUP BY
        CID
) tblMaxItem ON tblItem.CID=tblMaxItem.CID AND tblItem.created=tblMaxItem.maxCreated
WHERE
    tblOrders.status<>'disabled' AND tblCart.status='purchased' AND tblCart.BID='".$gbl_user['id']."' 
ORDER BY
    tblOrders.updated DESC

**Warning:** You are wide open to [SQL Injections](http://php.net/manual/en/security.database.sql-injection.php) and should really use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](http://php.net/manual/en/pdo.prepared-statements.php) or by [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Never trust any kind of input, especially that which comes from the client side. Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). — Dharman, Apr 11 '19 at 20:04
@Barmar So based on the duplicate marking, I looked at the post you referred and it looks like I should be using the MAX() statement. I have tried several variations and still can not get this to work. Anyone care to help? — user1646428, Apr 11 '19 at 20:12
The subquery you join with should use one of the techniques in those answers to get the latest row for each item. If you post what you tried, I'll reopen and we can show how to fix it. But we're not going to write it for you from scratch. — Barmar, Apr 11 '19 at 20:17
It seems like you didn't really read the answers there. Didn't you notice that they join the table with the subquery that uses `MAX()`? You have the MAX() subquery, but not the table itself. — Barmar, Apr 11 '19 at 20:27
@Barmar The updated version is using MAX() in the subquery. What do you mean 'not the table itself'? The post you pointed to is joining itself. This is a much more complex query than the one posted. — user1646428, Apr 11 '19 at 20:39
Like this query: `SELECT a.id, a.rev, a.contents FROM YourTable a INNER JOIN ( SELECT id, MAX(rev) rev FROM YourTable GROUP BY id ) b ON a.id = b.id AND a.rev = b.rev` It joins `YourTable` with the subquery that contains `MAX(rev)` — Barmar, Apr 11 '19 at 20:41
You join those to the tables you already have in your original query. — Barmar, Apr 11 '19 at 20:44
@Barmar That is just simply copying the query from the referenced post. That is not helping obviously since I've looked at it a dozen times at this point. As stated, that query is self joining. Lets try getting a resolution. Are you saying I have to make two sub queries? Basically adding another sub-query to the sub-query so the two can be self joined? — user1646428, Apr 11 '19 at 20:44
I've added the code. The last two joins are structured just like that sample query. — Barmar, Apr 11 '19 at 20:51

Barmar · Accepted Answer · 2019-04-12T17:59:43.173

0

You need to join with both ItemStatus and a subquery that gets the latest date for each item, so you can get other columns columns from that row.

SELECT
    tblOrders.id, tblOrders.status as orderStatus, tblOrders.created,
    tblCart.id AS cartID, tblCart.status AS cartStatus,tblCart.qty,
    tblCart.price, tblShop.title, tblItem.status AS itemStatus
FROM
    ".PREFIX."Orders tblOrders 
LEFT JOIN
    ".PREFIX."Cart tblCart ON tblOrders.id=tblCart.OID 
LEFT JOIN
    ".PREFIX."Shop tblShop ON tblCart.PID=tblShop.id 
LEFT JOIN
    ".PREFIX."ItemStatus tblItem ON tblCart.id=tblItem.CID 
LEFT JOIN (
    SELECT
        CID, MAX(created) AS maxCreated
    FROM
        ".PREFIX."ItemStatus
    WHERE
        status IN ('pending','refunded','cancelled','purchased','backordered','shipping','delivered')
    GROUP BY
        CID
) tblMaxItem ON tblItem.CID=tblMaxItem.CID AND tblItem.created=tblMaxItem.maxCreated
WHERE
    tblOrders.status<>'disabled' AND tblCart.status='purchased' AND tblCart.BID='".$gbl_user['id']."' 
ORDER BY
    tblOrders.updated DESC

edited Apr 12 '19 at 17:59

answered Apr 11 '19 at 20:49

Barmar

741,623
53
500
612

Thanks for posting a response! I have updated the code to fix syntax errors. Upon implementing the updated code, I am still not getting any different output from the original code posted. Any thoughts? – user1646428 Apr 11 '19 at 21:10
I also tried changing the 'JOIN' to 'INNER JOIN' as shown in the referenced post, but no changes still... – user1646428 Apr 11 '19 at 21:17
JOIN and INNER JOIN are the same thing. – Barmar Apr 11 '19 at 22:15
The last JOIN might need to be LEFT JOIN, since you're using LEFT JOIN on the previous line. – Barmar Apr 11 '19 at 22:16
Why did you add `status` to the subquery? The whole point of this is that you have to get `status` from `tblItem`, not from the subquery. – Barmar Apr 11 '19 at 22:17
Oh, I thought that was the query to pull the info from (since it should only be returning the last, single record). I was getting the SQL error "1054: Unknown column 'tblItem.itemStatus' in 'field list'" otherwise. I have corrected the code above and accepted your answer. Thanks a ton Barmar! – user1646428 Apr 12 '19 at 13:08
It should be `tblItem.status`. – Barmar Apr 12 '19 at 18:00

limit sub-query result set to last record

1 Answers1