I am trying to create a view for a comments table. In one of the cells it counts how much comments a comment has. This how the original table looks
+--+-------------------------------------+----------------+----------------+
|id|comments |commentOnContent|commentOnComment|
+--+-------------------------------------+----------------+----------------+
|1 |this is a comment |24 |0 |
+--+-------------------------------------+----------------+----------------+
|2 |this is a reply to that comment |0 |1 |
+--+-------------------------------------+----------------+----------------+
|3 |this is another reply to that comment|0 |1 |
+--+-------------------------------------+----------------+----------------+
|4 |this is a reply to a reply |0 |3 |
+--+-------------------------------------+----------------+----------------+
This is how the view looks
+--+-------------------------------------+-------+----------------+----------------+
|id|comments |replies|commentOnContent|commentOnComment|
+--+-------------------------------------+-------+----------------+----------------+
|1 |this is a comment |0 |24 |0 |
+--+-------------------------------------+-------+----------------+----------------+
|2 |this is a reply to that comment |0 |0 |1 |
+--+-------------------------------------+-------+----------------+----------------+
|3 |this is another reply to that comment|0 |0 |1 |
+--+-------------------------------------+-------+----------------+----------------+
|4 |this is a reply to a reply |0 |0 |3 |
+--+-------------------------------------+-------+----------------+----------------+
for the comment with an id of 1 the replies cell should equal three because it has two repliesas well as a reply to one of its replies. i can see how much replies it has with a simple statement select count(*) from comments where commentOnComment = id;. however if i want to add the the number of replies on its replies and replies on its replies on its replie im assuming that i would have to do some sort of recursive function but some of you may know that the problem is that stored functions can be in a view but cannot be recursive and stored procedures can be recursive but cant be in a view.
So in a nutshell the question is, is there any possible way to have a recursive statement to solve this problem of counting how many replies and replies on replies each comment has?
