Referencing outer query's tables in a subquery

Question

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.

Answer 1

very close...

WHERE t1.date = ( 
    SELECT MAX(date) 

change to

WHERE t1.date IN ( 
    SELECT MAX(date) 

Since your query does a MAX(), it will always return just one date... since your sub-select has the filter on the unique ID, it should give you what you want.

Answer 2

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.

Answer 3

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.