Menu
A subquery is a query that is nested inside a SELECT , INSERT , UPDATE , or DELETE statement, or inside another subquery. Joins and subqueries are both used to combine data from different tables into a single result.
¶ A CTE (common table expression) is a named subquery defined in a WITH clause. You can think of the CTE as a temporary view for use in the statement that defines the CTE. The CTE defines the temporary view's name, an optional list of column names, and a query expression (i.e. a SELECT statement).
A subquery cannot contain an ORDER BY clause. A subquery in an UPDATE statement cannot retrieve data from the same table in which data is to be updated. A subquery in a DELETE statement cannot retrieve data from the same table in which data is to be deleted.
In a SQL database query, a correlated subquery (also known as a synchronized subquery) is a subquery (a query nested inside another query) that uses values from the outer query.
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';
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With