Fetch data from database in a tree structure in sqlite

Question

In my application I have a tree structured table and I want to know that if I can fetch the tree structure data from sqlite database by simply applying a single query. Currently I am lopping it manually using a for loop and able to meet my requirement but I am not statisfied with that approach as its more complex, may be time consuming, etc.

My table table structure is as below,

parent_id |  cat_id

  null    |   1
   1      |   2
   1      |   3
   1      |   4
   4      |   5
   4      |   6

So, now above is my table structure and I want to fetch the tree structure like, If I pass parent_id as 1 in my query then it should return 2,3,4,5,6 as 1 is parent of 1, 2, 3, 4 and 4 is parent of 5,6 and also 4 is child of 1

So, input and output expected by me,

input    output

  1     2,3,4,5,6
  4     5, 6

So, it there a simple way to acheive the above tree structure by using sqlite query?

OR

There is no way to acheive this using sqlite query and I need to follow the current method or way that I am using it by manually looping all the category ids.

Answer 1

You can use group_concat method as it is available in sqlite.

For your question i have created one table called Trans and added parent_id and cat_id columns.

select parent_id, group_concat(cat_id) from trans group by parent_id;

I have table like this i have mentioned below.

and i got resultant like this :

Hope this will help you.