如何在单个查询中获取父级和子级注释？

Question

I have this table structure

I want to build query through which i can fetch all the rows using tst_id (which is called parent comment) and this tst_id should be matched with (if any) parent_tst_id (these are called child comments) .BUT the condition is parent comment(parent_tst_id=0) and child comment (tst_id) should be fetch at the same time and in same query.

tst_id  mem_id  from_id      testimonial       added            parent_tst_id   
3500    822     822     and KdevInd      1316613536         3497
3499    329     329     Reply by me      1316613489         3497
3498    821     821     a Fan comme      1316613307         3497
3497    329     399     Profile COm      1316613243         0

please help, thanks

Answer 1

You can self-join the table

SELECT child.*, parent.*
FROM comments AS child
LEFT JOIN comments AS parent ON child.parent_tst_id = parent.tst_id
WHERE child.tst_id = XXX