I am trying to get all rows which meets the parent child relation ship.
id Title parent_id 1200 A 1000 1201 B 1000 1202 C 1000 1203 D 1000 1204 E 1200 1205 F 1200 1206 G 1201 1207 H 1205 1208 I 1205 1209 J 1205
Now i have 1209 id, I want to retrieve all rows which satisfy parent child relation ship for 1209. Like here 1209 relates to 1205, 1205 relates to 1200, and 1200 relates to 1000.
I tried this query
SELECT * FROM `category` a left join category b on a.id=b.parent_id where a.id=1209
But this is giving me only one record. We are trying to get all rows which comes in this relationship.
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
if you’re just looking for it’s parent,grandparent,greatgrand parent you can use something like this.
SELECT id,title,parent_id FROM (SELECT id,title,parent_id, CASE WHEN id = 1209 THEN @id := parent_id WHEN id = @id THEN @id := parent_id END as checkId FROM Test ORDER BY id DESC) as T WHERE checkId IS NOT NULL
And just in case if you wanted to find all children, and grand children or great grand children of an id you can use this
SELECT id,title,parent_id FROM (SELECT id,title,parent_id, CASE WHEN id = 1200 THEN @idlist := CONCAT(id) WHEN FIND_IN_SET(parent_id,@idlist) THEN @idlist := CONCAT(@idlist,',',id) END as checkId FROM Test ORDER BY id ASC) as T WHERE checkId IS NOT NULL
query for finding all parents/grandparents/greatgrandparents of multiple children
SELECT id,title,parent_id FROM (SELECT id,title,parent_id, CASE WHEN id in (1209,1206) THEN @idlist := CONCAT(IFNULL(@idlist,''),',',parent_id) WHEN FIND_IN_SET(id,@idlist) THEN @idlist := CONCAT(@idlist,',',parent_id) END as checkId FROM Test ORDER BY id DESC)T WHERE checkId IS NOT NULL
Get other records with same parent_id as the given record
To get all the returns of other branches for the same company I belong to (identifed by same
parent_id in the table) I use this :
SELECT * FROM `sale_return` RET WHERE RET.`fk_contact_id` IN ( SELECT OTHERS.`contact_id` FROM `contact` ME INNER JOIN `contact` OTHERS ON ME.`parent_id` = OTHERS.`parent_id` WHERE ME.`contact_id` = $customer_id )
Hope it could help some of you.