Q: recursive mysql query to get child/parent results I have a comments table [ again :) ]...
comment_id
comment_parent_id
E.G. if there is comment1 (comment_id=1) and comment2 (comment_id=2) both have comment_parent_id=0 but then if someone replies (adds comment) to comment_id=1 it mean this will be comment3 (comment_id=3 but comment_parent_id=1)..
now to fetch and display all comments and sub-comments, I have to use a second query to check if it has sub comments, but I want to do it with one query, instead of recursive function, that will kill the server if there are 100-200 comments and they have have 1-2 sub-comments..
how can this be done..
thanks for your time..
regards

A: Table structure
posts
id
post_id
user_id

comments
id
post_id
comment_id
user_id
deleted

replies
id
post_id
reply_id
user_id
deleted

SELECT *
FROM posts p
LEFT JOIN comments c ON c.post_id = p.post_id AND NOT c.deleted
LEFT JOIN replies r ON r.post_id = p.post_id AND NOT r.deleted
WHERE p.user_id = $user_id

