Referencing outer query's tables in a subquery

Is it possible to reference an outer query in a subquery with MySQL? I know there are some cases where this is possible:

SELECT *
FROM table t1
WHERE t1.date = (
    SELECT MAX(date)
    FROM table t2
    WHERE t2.id = t1.id
);

But I'm wondering if something like this could work:

SELECT u.username, c._postCount
FROM User u
INNER JOIN (
    SELECT p.user, COUNT(*) AS _postCount
    FROM Posting p
    --# This is the reference I would need:
    WHERE p.user = u.id
    GROUP BY p.user
) c ON c.user = u.id
WHERE u.joinDate < '2009-10-10';

I know I could achieve the same using a GROUP BY or by pulling the outer WHERE clause into the sub-query, but I need this for automatic SQL generation and cannot use either alternative for various other reasons.

UPDATE: Sorry, the question led to some confusion: The first query is just a working example, to demonstrate what I don't need.

UPDATE 2: I need both u.id = p.user comparisons: The first counts users that joined before '2009-10-10', while the other one is a join condition that associates table rows correctly.

Isn't this what you're after?

SELECT u.username, c._postCount
FROM User u
INNER JOIN (
    SELECT p.user, COUNT(*) AS _postCount
    FROM Posting p
    GROUP BY p.user    
) c ON c.user = u.id
WHERE u.joinDate < '2009-10-10';

The reason this will work is that the nature of the join itself will filter on user. You don't need to have a WHERE clause explictly filtering on user.

i think that won't work, because you're referencing your derived table 'c' as part of a join.

however, you could just take out the WHERE p.user = u.id though and replace with a GROUP BY p.user in the derived table, because the ON c.user = u.id will have the same effect.

This solution is for postgresql. You could use LATERAL JOIN which is available in postgresql. Here is how you could use it in your query.

SELECT u.username, c._postCount
FROM User u
INNER JOIN LATERAL (
    SELECT p.user, COUNT(*) AS _postCount
    FROM Posting p
    WHERE p.user = u.id
    GROUP BY p.user
) c ON c.user = u.id
WHERE u.joinDate < '2009-10-10';

Here is a reference you could use. https://medium.com/kkempin/postgresqls-lateral-join-bfd6bd0199df

This is probably better:

SELECT u.username,
(SELECT COUNT(*) FROM Posting WHERE user = u.id) as _postCount
FROM User u WHERE u.joinDate < '2009-10-10';

> Is it possible to reference an outer query in a subquery with MySQL?

Yes, it is definitely possible. MySQL 8.0.14 and above:

> 13.2.11.9 Lateral Derived Tables > > A derived table cannot normally refer to (depend on) columns of preceding tables in the same FROM clause. As of MySQL 8.0.14, a derived table may be defined as a lateral derived table to specify that such references are permitted.

SELECT u.username, c._postCount
FROM User u,
LATERAL (
    SELECT p.user, COUNT(*) AS _postCount
    FROM Posting p
    --# This is the reference I would need:
    WHERE p.user = u.id  
    GROUP BY p.user
) c
WHERE u.joinDate < '2009-10-10';

And minified version(removing unnecessary grouping):

SELECT u.username, c._postCount
FROM User u,
LATERAL (
    SELECT COUNT(*) AS _postCount
    FROM Posting p
    WHERE p.user = u.id  
) c
WHERE u.joinDate < '2009-10-10';

Related reading: CROSS/OUTER APPLY in MySQL

This is how you do it to expand on the accepted answer

SELECT u.username, c._postCount
FROM User u
INNER JOIN (
    SELECT p.user, COUNT(*) AS _postCount
    FROM Posting p
    --# This is the reference I would need:
    --WHERE p.user = u.id ####REMOVE THIS####
    GROUP BY p.user
) c ON c.user = u.id
WHERE u.joinDate < '2009-10-10'
AND c.user = u.id -- ####ADD THIS####

This would work fine

SELECT u.id as userid,u.username, c._postCount
FROM User u
INNER JOIN (
    SELECT p.user, COUNT(*) AS _postCount
    FROM Posting p
    --# This is the reference I would need:
    WHERE p.user = userid
    GROUP BY p.user
) c ON c.user = u.id
WHERE u.joinDate < '2009-10-10';