Referencing outer query's tables in a subquery
- by soulmerge
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
) 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.
