Referencing previous table in join / subselect

Question

I'm usually very smart in writing even complex queries but I sometimes get stuck on really easy things like this one:

SELECT * FROM
orders o
JOIN (
    SELECT i.order_id, COUNT(*) AS order_item_count
    FROM order_item i WHERE i.order_id = o.order_id
) x
ON x.order_id = o.order_id

#1054 - Unknown column 'o.order_id' in 'where clause'

How can I reference the order_id inside the subselect?

What I don't want to do:

SELECT * FROM
orders o
JOIN order_item i WHERE i.order_id = o.order_id
ON i.order_id = o.order_id
GROUP BY o.order_id

I have multiple tables to join and I want the order_id not put inside a GROUP BY.

Answer 1

Sorry my question beeing a duplicate of How to specify the parent query field from within a subquery in mySQL?

So its solution is to put the subselect in the fields area rather than to join the subdataset:

SELECT
   *,
   (SELECT COUNT(i.order_id)
    FROM order_item i
    WHERE i.order_id = o.order_id) AS order_item_count
FROM orders o