Help with MySQL sub query

Basically what I have is a comments MySQL table. I have a column called 'parent_id' which tracks if a comment is a reply to another comment.

What I want to do is count all the comments. This was working fine but if you delete a comment and n开发者_Python百科ot the replies, it counts them as still active but intact they are deleted.

Here is my MySQL query which at the moment isn't working... Any ideas?

SELECT ic.* 
FROM roster_services_items_comments AS ic 
WHERE (SELECT icp.id FROM roster_services_items_comments AS icp 
       WHERE ic.parent_id = '' 
       OR (icp.id = ic.parent_id AND icp.deleted != 0) 
       LIMIT 1) IS NOT NULL

What about:

SELECT ic.*, COUNT(icp.parent_id) AS replies_count
FROM roster_services_items_comments AS ic
LEFT JOIN roster_services_items_comments AS icp ON ic.id = icp.parent_id
WHERE ic.deleted != 0
GROUP BY ic.id
HAVING ic.parent_id IS NULL

EDIT: Fixed the count to not count if a comment doesn't have any replies

You should probably add a trigger which marks replies of deleted to be deleted

the problem is that you need recursivly check the current comment for possible parents and parent's parents

you can create a recursive function, which determine has it deleted parents or not

Select Count(*)
From roster_services_items_comments As ic 
Where ( ic.parent_id = '' And deleted = 0 )
    Or ic.id Not In (
                    Select ic1.parent_id
                    From roster_services_items_comments As ic1
                    Where ic1.deleted = 1
                    )

Can parent_id really be an empty string? Wouldn't it make more sense for parent_id to be NULL when it was the top most comment? If that were true we'd have:

Select Count(*)
From roster_services_items_comments As ic 
Where ( ic.parent_id Is Null And deleted = 0 )
    Or ic.id Not In (
                    Select ic1.parent_id
                    From roster_services_items_comments As ic1
                    Where ic1.deleted = 1
                    )