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
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
Assuming tst_id available with you is always that of a parent comment, you can simply use the following query:
SELECT *
FROM comments
WHERE tst_id = XXX
OR parent_tst_id = XXX