How to join COUNT(*) from another JOINed table-CodePudding

I have 3 databases: Users, Posts, Comments.

I want to perform a query that gets information of all posts

SELECT
    Posts.id as postId,
    Users.id as authorId,
    Posts.title,
    Users.displayName,
    Posts.createdAt
FROM
    Users
INNER JOIN
    Posts
ON
    Users.id = Posts.authorId;

returns something like

 -------- ---------- --------- ------------- --------------------- 
| postId | authorId | title   | displayName | createdAt           |
 -------- ---------- --------- ------------- --------------------- 
|      1 |        1 | title 1 | Alice       | 2022-07-22 16:35:39 |
|      2 |        2 | title 2 | Bob         | 2022-07-22 16:35:47 |
 -------- ---------- --------- ------------- ---------------------

I want to join this with the result of

SELECT postId, COUNT(*) FROM Comments GROUP BY postId

which returns something like

 -------- ---------- 
| postId | COUNT(*) |
 -------- ---------- 
|      1 |        5 |
|      2 |        3 |
 -------- ----------

The final result I want to achieve is

 -------- ---------- --------- ------------- --------------------- -------------- 
| postId | authorId | title   | displayName | createdAt           | commentCount |
 -------- ---------- --------- ------------- --------------------- -------------- 
|      1 |        1 | title 1 | Alice       | 2022-07-22 16:35:39 | 5            |
|      2 |        2 | title 2 | Bob         | 2022-07-22 16:35:47 | 3            |
 -------- ---------- --------- ------------- --------------------- --------------

How should I write the SQL query? (I am using MySQL)

CodePudding user response：

You have 3 tables(?) and you can try this:

SELECT
    P.ID AS POSTID,
    U.ID AS AUTHORID,
    P.TITLE,
    U.DISPLAYNAME,
    P.CREATEDAT,
    C.CNT
FROM
    USERS U
INNER JOIN
    POSTS P
ON
    U.ID = P.AUTHORID
    JOIN (SELECT POSTID, COUNT(*) CNT FROM COMMENTS GROUP BY POSTID) C
    ON C.POSTID = P.ID;

CodePudding user response：

You can use a subquery (a "scalar subquery") to get the number of comments for each post.

For example:

SELECT
    p.id as postId,
    u.id as authorId,
    p.title,
    u.displayName,
    p.createdAt,
    (select count(*) from comments c where c.postid = p.id) as comment_count
FROM
    Users u
INNER JOIN 
    Posts p
ON
    u.id = p.authorId;

I added aliases to the tables to improve readability.

CodePudding user response：

You can use CTE (Common Table Expressions) over here. Below is the query:

with comment_count as 
(
SELECT postId, COUNT(*) as commentCount FROM Comments GROUP BY postId
),
post_processed as 
(
SELECT
    Posts.id as postId,
    Users.id as authorId,
    Posts.title,
    Users.displayName,
    Posts.createdAt
FROM
    Users,
    Posts
where
    Users.id = Posts.authorId
)
select 
pp.*,
cc.commentCount
from 
post_processed pp,
comment_count cc
where pp.postId = cc.postId;