Joining two SQL tables with GROUP BY not achieving desired results

Question

For example, I have a shop order database, and two tables in it - ORDERS and ORDERSTATUS.

Table : orders
--------------------------------------------
OrderID | OrderItems | AddedTimeStamp      |
--------------------------------------------
1       | Apples     | 2009-12-22 13:15:18 |
--------------------------------------------
2       | Bananas    | 2009-12-22 14:15:24 |
--------------------------------------------

Table : orderstatus
--------------------------------------------------------------------
StatusID | OrderID | Status   | AssignedUser | StatusTimestamp     |
--------------------------------------------------------------------
1        | 1       | Received | JohnSmith    | 2009-12-22 14:15:24 |
--------------------------------------------------------------------
2        | 2       | Received | MaryJane     | 2009-12-22 14:15:24 |
--------------------------------------------------------------------
3        | 1       | Process  | JohnSmith    | 2009-12-22 14:15:24 |
--------------------------------------------------------------------
4        | 2       | Process  | MaryJane     | 2009-12-22 14:15:24 |
--------------------------------------------------------------------
5        | 2       | Deliver  | MaryJane     | 2009-12-22 14:15:24 |
--------------------------------------------------------------------

I am running this SQL query :

SELECT od.orderid, od.orderitems, os.status, os.assigneduser
FROM orders AS od INNER JOIN orderstatus AS os
ON od.orderid = os.orderid
GROUP BY os.orderid
ORDER BY os.orderid ASC

This returns me :

------------------------------------------------
OrderID | OrderItems | Status   | AssignedUser |
------------------------------------------------
1       | Apples     | Received | JohnSmith    |
------------------------------------------------
2       | Bananas    | Received | MaryJane     |
------------------------------------------------

What I would like is :

------------------------------------------------
OrderID | OrderItems | Status   | AssignedUser |
------------------------------------------------
1       | Apples     | Process  | JohnSmith    |
------------------------------------------------
2       | Bananas    | Deliver  | MaryJane     |
------------------------------------------------

I'm quite new to MySQL queries, but I've been banging my head for the past 4 hours - can someone help?? TIA.

EDIT : The basic objective is I want to show the latest status for the orders.

Answer 1

Its tough to get what you want without more information. All of the records in the orderstatus table have the same timestamp. It seems you just want the ones with the other status, but there isn't any way to differentiate the different statuses.

In order to do this, we need to tell MySQL how to pick the best row. Look closely... How would you know if you were a machine, which row to return?

The best I can guess is that you want the latest orderstatus record (max statusid). If that is the case, then here is a script that will work:

SELECT
    od.orderid, 
    od.orderitems, 
    os.status, 
    os.assigneduser
FROM orders AS od
INNER JOIN orderstatus AS os ON od.orderid = os.orderid
INNER JOIN (
    SELECT
        MAX(StatusID) AS StatusID,
        OrderId
    FROM orderstatus
    GROUP BY OrderId
) as maxos ON maxos.StatusID = os.StatusID
GROUP BY os.orderid
ORDER BY os.orderid ASC

I'm assuming your example is not accurate, and that the timestamp will actually be different in real life.

In that case, you can use the same script from above, but get the max(timestamp) instead of max(statusid).

The other thing you can do is normalize the status column (create a table for status codes, and add a column designating the order. Then you can get the max(that new order column) using the script above.

Answer 2

Use:

   SELECT o.orderid,
          o.orderitems,
          os.status,
          os.assigneduser
     FROM ORDERS o
LEFT JOIN ORDERSTATUS os ON os.orderid = o.orderid
     JOIN (SELECT os.orderid,
                  MAX(os.statustimestamp) 'm_statustimestamp'
             FROM ORDERSTATUS os
         GROUP BY os.orderid) x ON x.orderid = o.orderid
                               AND x.m_statustimestamp = os.status_timestamp

I used the timestamp as the way to find the latest record, though your data claims they will all be the same.

If you normalized your ORDERSTATUS.status column to a separate table (say ORDER_STATUS_CODE, with two columns - code and description), you'd have an easier time because assuming the use of an integer based key, you could use MAX() or MIN() depending on how the column was setup to get the highest status for a given order.

Answer 3

Looks like you need to know the latest status and user per order. The join, group and order by will not work. You can achieve that result several ways and subqueries is one. Try this:

SELECT od.orderid, od.orderitems, 
(SELECT os.status FROM os.orderstatus AS os WHERE os.orderid = od.orderid ORDER BY os.StatusTimestamp LIMIT 1) AS status, 
(SELECT os.assigneduser FROM os.orderstatus AS os WHERE os.orderid = od.orderid ORDER BY os.StatusTimestamp LIMIT 1) AS assigneduser, 
FROM orders AS od

Take this with a grain of salt though. i don't know which version of MySql you're running and i'm more familiar with Sql Server than MySql. You might have to massage the query some.