All Questions
Tagged with recursive-query sqlite
64 questions
-1
votes
1
answer
67
views
Find none overlapping intervals using sqlite
I have a data base with ids and time intervals example:
TimeId
Start
End
1
0
10
1
2
13
1
11
21
1
15
30
2
0
10
2
2
13
2
11
21
2
15
30
I want to write a query which selects none-overlapping intervals....
-1
votes
1
answer
132
views
sqlite recursive common table expression using pragma foreign_key_list
I'm trying to enumerate all parent tables and their parent tables for a given table.
the following sqlite script sets up a parent>child>grandchild hierarchy, does select pragma_foreign_key_list ...
- 179
0
votes
2
answers
282
views
Recursive lookup on Multiple Tables with conditional
I have multiple tables from which I need to extract Parent/Child info for each item in one of the tables. So for example:
Table1:
id
Name
ParentIDFromTable2
Weight
Height
Description
...
- 93
2
votes
1
answer
168
views
SQLite - recursive query to obtain value based on parent
I have an SQLite table that has a structure similar to the following:
ID
TYPE
PARENT_ID
1000
A
1050
1001
B
1067
1002
1000
1003
C
1100
1004
B
1378
1005
1002
1006
I need to create an SQL query that ...
-2
votes
1
answer
47
views
How to find remaining date range in sqlite
I have a sqlite database like
ID Fromdate Todate
1 2023-04-02 2023-04-07
2 2023-04-05 2023-04-06
3 2023-04-09 2023-04-11
2 2023-04-09 2023-04-16
from temptable.
Now i am getting stack ...
0
votes
1
answer
149
views
Why does my recursive query stop after one step?
I would like to get a list of employees in an organization that ultimately report to a given person (a flattened sub-tree of the org chart). I created a sample organization (in SQLite, view on DB ...
- 30.2k
0
votes
1
answer
161
views
SQLite merge/union/flatten subqueries
I'm trying to write some SQL code in the same vein as the "Outlandish Recursive Query" examples at https://www.sqlite.org/lang_with.html.
In particular, I'm trying to make a prime sieve ...
- 665
0
votes
2
answers
94
views
recursive SQLite SELECT: can’t wrap my head around it
I have this SQLite table:
CREATE TABLE seed (
code TEXT PRIMARY KEY,
name TEXT,
grams_per_seed REAL,
mother TEXT REFERENCES seed (code) DEFERRABLE INITIALLY DEFERRED,
notes TEXT,
...
- 2,077
1
vote
1
answer
160
views
SQLite recursive query, to collect filepath from filenames
I have a SQLite table, below is its structure
TABLE file(
"ID" STRING PRIMARY KEY,
"filename" string NOT NULL,
"parent" string,
"is_folder" ...
- 63
0
votes
1
answer
96
views
How to fill missing numbers with Null?
In SQLite how to fill in null for missing numbers from a range of 1-10 when using Group By? I have a table called DataTest:
DataID
theData
1
50
2
38
2
48
4
38
5
48
8
39
9
50
9
60
10
90
If I do SELECT ...
0
votes
1
answer
476
views
SQLite Recursive Function
I have a view in SQLite where two columns need to follow a recursive formula. Example input data is below:
Date
Ca
Cb
Cc
2020-01-01
NULL
NULL
100.0
2020-01-02
0.1
NULL
NULL
2020-01-03
0.2
NULL
NULL
...
- 1,134
1
vote
1
answer
66
views
A->B->C relationship management in a 2 column sqlite database
Hello Everyone and thank you in advance for the help.
I have setup a correspondance table that looks like this
Old
New
A
B
B
C
A
D
C
D
Of course this is a log of correspondance between one state to ...
- 13
0
votes
1
answer
230
views
How to split these records into individual new ones?
I want to search if a string exists in column2 (site_id) then put that string in a new table with the value of its before column in that row. The table has only 2 columns and the site_id column may ...
- 23
2
votes
1
answer
2k
views
SQLite recursive CTE to calculate number of items in list (of lists)
Each List can have multiple ListItems (1 --> n). And a list can have a parent list.
List 1
|
|-- List Item 1
|
|-- List 2
| |
| ...
- 5,037
1
vote
1
answer
249
views
How to join generated datetimes with data in SQLite database?
I have a table with data from a sensor created like that:
CREATE TABLE IF NOT EXISTS "aqi" (
"time" datetime,
"pm25" real,
"pm10" real
);
When is sensor ...
- 107