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.

Answer 1

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