0

I have a table as below:

Id     Name    Parent_id
1      One     0
2      Two     0
3      Three   2
4      Four    1
5      Five    0
6      Six     4
7      Seven   1
8      Eight   3
9      Nine    5
10     Ten     8

SQL Query which works fine if i provide single id as a value

SELECT T2.id, T2.name
FROM (

    SELECT @r AS _id, (SELECT @r := parent_id FROM category WHERE id = _id) AS parent_id

    FROM (SELECT @r := 10 ) vars, category h

    WHERE @r <> 0) T1

JOIN category T2
ON T1._id = T2.id

Here is the SQL Fiddle for the above working query.

I have to assign Array here FROM (SELECT @r := 10 ) instead of value 10 But i don't know how to apply that

I also need to iterate all the values of the array

I have a situation where i need to pass Array of id as a value and get the parent tree of all the respective id's in the array. I tried many things search for it over the net, still am not able to tackle it out as i am not much known to sql queries.

Also if any optimized method is there to achieve the same, will be highly appreciated.

Thanks :)

kanudo
  • 2,119
  • 1
  • 17
  • 33
  • Possible duplicate of [How to pass a variable to a IN clause?](http://stackoverflow.com/questions/13116042/how-to-pass-a-variable-to-a-in-clause) – Bacon Bits Feb 10 '16 at 13:49
  • @BaconBits i seen the answer but that did not solve my problem. – kanudo Feb 10 '16 at 14:37

0 Answers0