Android – select from a table based on relationships saved in another table

My situation

I use SQLite on Android to store some data. This data is all in the same table, but each row can have one or more relationships with other rows in the same table. These relationships are saved in another table. Think like this:

In Table 1, there may be a row with ID 0, in which two child nodes have ID 1 and 2. These two child nodes will be saved in Table 1 again, but in Table 2, a mapping will be created for each child node from ID. 0 to their own ID. the table may be as follows:

+---------------------------+     
|           Table 1         |     
+------+------+------+------+     
|  ID  |   .... Data ....   |     
+------+------+------+------+     
|  0   | ...  | ...  | ...  |   <--- This would be the parent of rows 1 & 2         
|  1   | ...  | ...  | ...  |        as indicated in the other table
|  2   | ...  | ...  | ...  |      
|  3   | ...  | ...  | ...  |     

+----------------------------+
|           Table 2          | 
+-------------+--------------+
|  Parent ID  |   Child ID   |
|      0      |       1      |   <-- This means that row 0 has
|      0      |       2      |   <-- 2 children with the ids 1 and 2  
|      2      |       5      |          
|      3      |       2      |   <-- Each row can have multiple parents and/or children

What I wanted to do was to select some arbitrary where clauses from table 1. If this where clause matches row 0, for example, I also need to select the child nodes of row 0 and those child nodes, and so on. Since I am generally explaining things, let me repeat:

If I want to run this selection:

SELECT * FROM TABLE1 WHERE ...

I will get this result:

+------+------+------+------+     
|  ID  |   .... Data ....   |     
+------+------+------+------+     
|  0   | ...  | ...  | ...  |      
|  3   | ...  | ...  | ...  |

But what I want is:

+------+---------+------+------+------+     
|  ID  | isChild |   .... Data ....   |     
+------+---------+------+------+------+     
|  0   |    0    | ...  | ...  | ...  |   <--- This row along with row 3 is what actually matches the where clause            
|  1   |    1    | ...  | ...  | ...  |
|  2   |    1    | ...  | ...  | ...  |      
|  5   |    2    | ...  | ...  | ...  |
|  3   |    0    | ...  | ...  | ...  |   <--- This row along with row 0 is what actually matches the where clause            
|  2   |    1    | ...  | ...  | ...  |
|  5   |    2    | ...  | ...  | ...  |

Only lines 1 and 3 actually match the where clause. The order of children is not important, but they should be after their parents, and the "ischild" column will be used to indicate whether the row is a child and what child it is

Note the third row at the top of the above output, the row with ID 2. It has 2 in "ischild" because it is both a child node and a child node of the above row. You can treat the whole above output as such a tree:

- 0 
   - 1      <-- 1 is a child of 0
   - 2      <-- 2 is a child of 0
      - 5   <-- 5 is a child of 2
- 3
   - 2      <-- 2 is a child of 3
      - 5   <-- 5 is a child of 2

The "ischild" column mainly tells you which level of the tree

problem

So far, I have implemented multiple choices. I first select rows from table1, take the ID from each row, and then select the mapping of each row from table2. Through these mappings, I will select sub items from table1, and then I will look for the sub item mapping in table2 again. I can see that this can quickly lead to huge performance problems, and it is really very slow and does not require genius

Since then, I've been trying to improve this by reducing the number of choices required, but now I've hit a wall. I've achieved any improvement I can think of, and it works to a large extent, but if you deal with large data sets, everything slows down exponentially, I didn't see any other way I could improve it in the code. I began to think and came to the conclusion that if I could choose all the content at once in some way in the way described above, it would solve a lot of problems for me

I've tried to solve this problem so far

Since I can't make further improvements in the code, I turned my attention to SQL. I have made many irrelevant improvements, and achieved great performance improvement by implementing triggers to perform the most common tasks (such as creating and deleting mappings in table2). I always hope I can solve this problem in a similar way

I've tried all kinds of join or union, but nothing seems to work as I expected. I have a feeling that I may solve this problem in the wrong way. So far, I haven't tried to include the "ischild" column

This is a link to the SQLFiddle I use to test my selects

When I started studying this, I foolishly thought that a simple join could solve the problem, but I doubt that at this point, I'm not sure what I want to do or even possible (in an effective way)

This problem makes me realize that I don't know much about SQL. I would appreciate it if some SQL wizards could tell me how simple the solution is! (although I doubt that the solution to my problem is actually not that simple)

Remember, this issue is specifically about SQLite on Android. But I try to make this issue as general as possible because it also applies to many other SQL implementations or operating systems

If you have a very good answer to this question, a simple solution that shocked me and a good explanation, I will reward you without hesitation

resolvent:

To read children recursively, you must use recursive common table expression

However, this was introduced in SQLite 3.8.3, so your Android device is unlikely to support it

You must continue to use multiple queries, or use your SQLite version with NDK