Referencing outer query's tables in a subquery

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 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';