Question 21156 in Mysql Question posted in
The official MySQL documentation can be found here.

Relate two columns in the same table – Mysql

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:

author_id content comment_id replied_to
12 nice post! 1 0
7 isn’t it? 2 1
12 for sure 3 2
4 i didn’t like it so much 4 0
7 why is that? 5 4
11 please elaborate 6 4

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?

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search