Why I can not join this query with Max date?

Question

I have an issue with the following mySQL query where it fails when Max date is introduced as shown below. I get the following error

Error Code: 1054. Unknown column 'order_items.ORDER_ITEM_ID' in 'where clause'

SET @UserID = 160;
SET @OrderDateTime = '2018-11-13 09:23:45';

SELECT 
order_items.ORDER_ID,
listing_region.LIST_REGION_REGION_ID,
listings.LISTING_ID,
order_items.ORDER_REQUIRED_DATE_TIME,
listings.LISTING_NICK_NAME,
order_items.ORDER_QUANTITY,
order_price.ORDER_PRICE_ID,
order_items.ORDER_PORTION_SIZE,
t.LATEST_DATE,
t.ORDER_STATUS


FROM order_status_change, order_items 

INNER JOIN listings ON listings.LISTING_ID = order_items.ORDER_LISTING_ID
INNER JOIN listing_region ON listing_region.LIST_REGION_LISTING_ID = listings.LISTING_ID
INNER JOIN order_price ON order_price.ORDERP_ITEM_ID = order_items.ORDER_ITEM_ID
INNER JOIN 
  (
     SELECT MAX(order_status_change.ORDER_STATUS_CHANGE_DATETIME) AS LATEST_DATE, order_status_change.ORDER_ITEM_ID, order_status_change.ORDER_STATUS
     FROM order_status_change
     WHERE order_status_change.ORDER_ITEM_ID = order_items.ORDER_ITEM_ID
  ) AS t ON order_status_change.ORDER_ITEM_ID = t.ORDER_ITEM_ID AND order_status_change.ORDER_STATUS_CHANGE_DATETIME = t.LATEST_DATE 

WHERE ((order_items.ORDER_USER_ID = @UserID) AND DATE(order_items.ORDER_REQUIRED_DATE_TIME) = DATE(@OrderDateTime))

Any help ?

Please don't use Old comma based Implicit joins and switch to Modern [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308), which will avoid these inadvertent errors. — Madhur Bhaiya, Nov 29 '18 at 06:30
A correlated Subquery allows for accessing tables from outer level; but yours is a Derived Table (subquery in the `FROM` clause). This does not have access to tables specified in the outer level. — Madhur Bhaiya, Nov 29 '18 at 06:31
@Madhur,What do you mean by old comma based JOIN ? This looks like what you provided.... — PCG, Nov 29 '18 at 06:37
In my first comment, there is a URL linked. Do go through that. — Madhur Bhaiya, Nov 29 '18 at 06:38
Whole idea here is to get the latest record from order_status_change for the STATUS. Is there a better way to get it ? — PCG, Nov 29 '18 at 06:38
Please post some sample data and corresponding expected output. Based on that, I can try formulating a better query. — Madhur Bhaiya, Nov 29 '18 at 07:06

score 1 · Answer 1 · answered Nov 29 '18 at 07:24

Here's a simplified version to illustrate the problem.

DROP TABLE IF exists t,t1;

create table t (id int);
create table t1(id int,dt date);

insert into t values (1),(2);
insert into t1 values (1,'2018-01-01'),(1,'2018-02-01'),(2,'2018-01-01');

select t.*,t2.maxdt
from t
join (select max(dt) maxdt,t1.id from t1 where t1.id = t.id) t2
    on t2.id = t.id;

ERROR 1054 (42S22): Unknown column 't.id' in 'where clause'

You could group by in the sub query and then the on clause will come into play

select t.*,t2.maxdt
from t
join (select max(dt) maxdt,t1.id from t1 group by t1.id) t2
    on t2.id = t.id;

+------+------------+
| id   | maxdt      |
+------+------------+
|    1 | 2018-02-01 |
|    2 | 2018-01-01 |
+------+------------+
2 rows in set (0.00 sec)

If you want an answer closer to your problem please add sample data and expected output to the question as text of to sqlfiddle.

Great help Thank you to you too. Explanation helps a lot. – PCG Nov 29 '18 at 15:16 — PCG, Nov 29 '18 at 15:16

Paul Maxwell · Accepted Answer · 2018-11-29T07:50:03.333

I have assumed you can join order_status_change on order_items.ID = order_status_change.ORDER_ITEM_ID

If that is valid then I think this will achieve what you are after:

SET @UserID = 160;
SET @OrderDateTime = '2018-11-13 09:23:45';


SELECT
    order_items.ORDER_ID
  , listing_region.LIST_REGION_REGION_ID
  , listings.LISTING_ID
  , order_items.ORDER_REQUIRED_DATE_TIME
  , listings.LISTING_NICK_NAME
  , order_items.ORDER_QUANTITY
  , order_price.ORDER_PRICE_ID
  , order_items.ORDER_PORTION_SIZE
  , t.LATEST_DATE
  , order_status_change.ORDER_STATUS
FROM order_items
INNER JOIN listings ON listings.LISTING_ID = order_items.ORDER_LISTING_ID
INNER JOIN listing_region ON listing_region.LIST_REGION_LISTING_ID = listings.LISTING_ID
INNER JOIN order_price ON order_price.ORDERP_ITEM_ID = order_items.ORDER_ITEM_ID

INNER JOIN order_status_change ON order_items.ID = order_status_change.ORDER_ITEM_ID
INNER JOIN (
        SELECT
            MAX( mc.ORDER_STATUS_CHANGE_DATETIME ) AS LATEST_DATE
          , mc.ORDER_ITEM_ID
        FROM order_status_change AS mc
        GROUP BY
            mc.ORDER_ITEM_ID
    ) AS t
             ON order_status_change.ORDER_ITEM_ID = t.ORDER_ITEM_ID
            AND order_status_change.ORDER_STATUS_CHANGE_DATETIME = t.LATEST_DATE 

WHERE order_items.ORDER_USER_ID = @UserID
AND DATE( order_items.ORDER_REQUIRED_DATE_TIME ) = DATE( @OrderDateTime )

You need to avoid this in future:

FROM order_status_change  , order_items

That comma between the 2 table names IS a join, but it is from an older syntax and it is LOWER in precedence than the other joins of your query. Also, by default this comma based join acts as an equivalent to a cross join which MULTIPLIES the number of rows. In brief, please do NOT USE commas between table names.

The other issue is that you were missing a group by clause and I believe you just want to get the "latest" date from this aggregation, once that is determined link back to that table to get the status relevant to that date. (i.e. you can't group by status in the subquery, otherwise you get the latest dateS (one for each status).

Thank you very much for the explanation and it works . . . ! . Fortunately there is only one more working query that has the same comma seperated sysntax with JOIN and will correct it now. — PCG, Nov 29 '18 at 15:15

Why I can not join this query with Max date?

2 Answers2