I have table which has a parent child relationship as shown below
id Name parent_id
1 F1 0
2 F2 0
3 S1 1
4 S3 2
5 S4 4
6 S5 4
7 S6 4
Presently i would like to get the date such that when i query for Id='2', i would need all the childs associated with that parent id.
with the below query i'm able to get only 1 level childs
SELECT r.* FROM category r
left JOIN category a on a.parent_id = r.id
left join category b on b.parent_id = a.id
left join category c on c.parent_id = b.id
where r.id = '2'
here is the result i would like to see
id Name parent_id
4 S3 2
5 S4 4
6 S5 4
7 S6 4
