I have a MySQL query which should count the amount of replies to your comments with relations to other columns.

I try to "relate" two columns together but can't figure out how to create a correct query.

The table somewhat looks like this:

So to know how many replies you have, we need to relate replied_to and comment_id. But also we need to determine the relation between the comment_id and to whom it belongs to.

Let's assume that I am a user with author_id 4 and two people replied to my comment which are the comment_id 5 and 6. The query should return 2.

I've tried something like this (this is an incorrect query, since you can't do something like comment_id.author_id = 4):

SELECT COUNT(*) FROM comments WHERE replied_to = comment_id AND comment_id.author_id = 4;

How to approach this kind of query, or is the table structure incorrectly done and should be in another table instead?

CodePudding user response：

Using a self-join can help in situations like this.

SELECT COUNT(*) AS count
FROM comments AS post
JOIN comments AS reply ON reply.replied_to = post.comment_id 
WHERE post.author_id = 4;

CodePudding user response：

Use the operator IN with a subquery in the WHERE clause:

SELECT COUNT(*) 
FROM comments 
WHERE replied_to IN (SELECT comment_id FROM comments WHERE author_id = 4);