Join with sub-query on the same table as main table

[yongqilei]

Hi Team,

I have one scenario to find only records with maximum sequence, the SQL I want to achieve is:

select
  order.*,
  account.account_name
from order
join account on account.account_id = order.account_id
join (
  select order_key, max(item_seq) as `max_item_seq`
  from order
  group by order_key
) t2 on t2.order_key = order.order_key and t2.max_item_seq = order.item_seq

And in Java code:

var order = OrderDynamicSupport.order;
var order2 = OrderDynamicSupport.order2.withAlias("o2");
var account = AccountDynamicSupport.account;

var selectStatementProvider = select(order.allColumns())
    .from(order)
    .join(account).on(account.account_id, equalTo(order.account_id))
    .join(select(order2.order_key, max(order2.item_seq).as("max_item_seq")).from(order2).groupBy(order2.order_key), "t2")
    .on(order2.order_key.qualifiedWith("t2"), equalTo(order.order_key), and(DerivedColumn.of("max_item_seq", "t2"), equalTo(order.item_seq)))
    .build().render(RenderingStrategies.MYBATIS3);

The rendered SQL in the selectStatementProvider will be like:

select *,
    account.account_name
from order
join account on account.account_id = account_id
# ...omit the rest

All the columns from table order lose the table alias and this is causing ambiguous issue, the interesting thing is when I remove the sub-query join condition, everything is working fine, the rendered SQL contains table alias of main table.

Could you help on this?

[yongqilei]

Please ignore this issue, it is resolved by adding some other alias to the main table