Get all data of parent child relation-ship from same table in mysql

Question

I am trying to get all rows which meets the parent child relation ship. for example

  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.

MySQL has little support for hierarchal relationships/recursive queries. You might find this interesting: http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database. — Gordon Linoff, Apr 09 '16 at 22:20
Thanks. Interesting topic. But don't have too much time to read this briefly. I will read this later. — ISHAN DOGRA, Apr 09 '16 at 22:38
select * from category as child join category as parent on parent.parent_id = child.id this query giving me all data but when i am passing id=1209 in it, Then its giving me again one row. — ISHAN DOGRA, Apr 09 '16 at 22:38

Tin Tran · Accepted Answer · 2016-04-10T16:21:18.487

15

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

sqlfiddle

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

sqlfiddle for finding children

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

sqlfiddle

edited Apr 10 '16 at 16:21

answered Apr 09 '16 at 22:38

Tin Tran

6,194
3
19
34

Great. Can i use this for nth level? – ISHAN DOGRA Apr 09 '16 at 22:52
yeah try it out as long as your child's ids are always larger than parent's id it should work – Tin Tran Apr 09 '16 at 22:53
Yes. I think that always greater then parent's id. Its working. Thanks. I spent 6 hours on this but failed. – ISHAN DOGRA Apr 09 '16 at 22:58
I added a query for finding all children/grandchildren/greatgrandchildren nth level just in case you needed it. – Tin Tran Apr 09 '16 at 23:16
Sorry, But i just found bug. I replaced your query with 'IN' clause for id, But its not working correctly. Here is modified query http://pastebin.com/hP5KqWYd – ISHAN DOGRA Apr 10 '16 at 15:07
I have added a third query (bottom of answer) for finding parents/grandparents/greatgrandparents/ for multiple children. try it out and let me know – Tin Tran Apr 10 '16 at 16:22
Sorry. but not working correctly. You can check my data and result here https://postimg.org/image/5swb7dw53/ https://postimg.org/image/3n20cvson/ – ISHAN DOGRA Apr 10 '16 at 17:33
Here is the fiddle if you want http://sqlfiddle.com/#!9/56359/1 You can see menu4 is not showing. – ISHAN DOGRA Apr 10 '16 at 18:06
You didn't use the last query at bottom of answer (3rd query) – Tin Tran Apr 10 '16 at 18:16
Strange, I copied your query but not working on my phpmyadmin. – ISHAN DOGRA Apr 10 '16 at 18:37

score 0 · Answer 2 · answered Oct 11 '21 at 09:18

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.

Get all data of parent child relation-ship from same table in mysql

2 Answers2

Linked