Questions tagged [recursive]
Using recursion to solve database problems.
262 questions
2
votes
2
answers
578
views
Recursively get ids of tree
I have a table foo.
So we have a hiearchical object structure where on foo has one parent (or none) and a list of children.
What I want is a sql statement that lists all id:s that can be found either ...
- 664
0
votes
1
answer
199
views
A way to process a CTE once?
I have a view that has serious performance issues, because I am linking it to a CTE that uses another view, with the same table references.
WITH
splits(ID, [NEW TEU])
AS
(SELECT s.[DTL ID] AS ...
- 295
2
votes
1
answer
1k
views
Why is my recursive CTE so much slower on Azure SQL?
I have this simple recursive CTE for a hierarchy of folders and their paths:
WITH paths AS (
SELECT Id, ParentId, Name AS [Path] FROM Folders
WHERE ParentId IS NULL
UNION ALL
SELECT ...
- 213
0
votes
1
answer
146
views
calculate or get price of parent items on bill of materials
i want to calculate or get the price of parent items on bill of materials query
here is the fiddle-> https://www.db-fiddle.com/f/o3jLgZxKNLG14mna8QGdVN/6
please note in the second row, betax has ...
- 9
10
votes
1
answer
3k
views
Simplifying Function with Recursive CTE and/or Window Function
I'm trying to come up with a Recursive CTE and/or Window Function to create a function.
After days, I've boiled the function down to (pseudocode)
where I have N and B, and need to generate E:
En =...
- 232
0
votes
1
answer
3k
views
How to aggregate over a "multi source" with recursive, in postgres?
Imagine I have a table "departments" like this:
name TEXT
parent_department TEXT (nullable)
I also have a "budgets" table like this:
department_name TEXT
budget INTEGER
I need to "traverse" this ...
- 330
1
vote
1
answer
510
views
Recursive Parent-Child Location Relationship Cache as View
Here is my data structure:
CREATE TABLE Locations(
LocID int IDENTITY(1,1) NOT NULL,
LocationTypeID int NOT NULL,
ParentID int NULL,
LocNum varchar(50) NULL,
LocName varchar(250) ...
- 127
3
votes
1
answer
860
views
Get the total count of every child foreign keys
I have been trying this in SQL EXPRESS 2016. Here is my problem:
I have a tree like structure DB(Parent > child > grandchild >...) and in the lower level there is a relation with another table.
...
- 43
0
votes
1
answer
461
views
How do I join on a timestamp and include NULL entries?
I am attempting a query to compare the sum of daily accounting invoices to a daily budget. The budget is stored as one value for a month, so I divide that amount by the total number of days as in ...
0
votes
1
answer
228
views
SQL Server 2012 - Common Table Expression (CTE) returning more rows than it should
CTE SQLFiddle
I have one lifetable with 121 rows and 3 columns (age, male and female). I have to fetch all the 121 rows considering the gender. If the gender is "Male" I fetch the column "Male" from ...
- 133
7
votes
1
answer
1k
views
Recursivly get a Tree Via self joined table
Using other questions here and Postgresql documentation I've managed to build a many-to-many self joined table.
However adding a WHERE clause is giving me trouble.
Problem:
A Category can have many ...
- 171
0
votes
1
answer
2k
views
Calculate products count based on category tree table
It is necessary to calculate how many products were created in each category, of course, that the products created in a child must be considered for the parent.
How can i do that SQL/Postgresql?
...
- 3
0
votes
1
answer
224
views
Insert dummy data based on data time found in column
while doing data collection, I've come to realize that some of the data are missing and could not be re-collected due to some issue with the program. One of the suggestions given was to replace the ...
- 13
1
vote
1
answer
2k
views
54001 stack depth limit exceeded in recursive stable function [closed]
I have a function which calls itself to resolve a hierarchy. Without any volatility declaration, it gives me the desired result (that means the recursion ends). As a performance optimization, I ...
- 317
7
votes
1
answer
4k
views
Recursive CTE based on function values significantly slower on Postgres 12 than on 11
Following up on my question about some queries in Postgres 12 being slower than in 11 I think, I was able to narrow down the problem. It seems like one recursive CTE based on function values is the ...
- 479
1
vote
1
answer
84
views
How can I create a recursive query over a column that has gaps?
I've adapted the recursive query from here in order to calculate ELO ratings (like in chess) from a list of game outcomes on-the-fly.
Given a table of games like:
id | home_player | away_player | ...
- 113
1
vote
1
answer
454
views
How to use a recursive CTE to get ancestors in a hierarchy
create table division (
id serial primary key,
name varchar not null
);
-- nested sets table
create table location (
id serial primary key,
name varchar not null,
division_id integer not ...
- 111
0
votes
2
answers
2k
views
How to recursively update a same string?
I have a table with a column containing names like this:
id, employee
1, Mr. John Cole Thornton
2, Mr. Paul George Mckenzie
3, Mr. George Mick McDoughal
4, Ms. Emily Suzan Flemming
5, Mr. Alan ...
- 191
-1
votes
2
answers
1k
views
Start at condition and stop when criteria is met
i'm new to MS SQL and i have to carry out the following task.
What i want to output is the time of the first occurence of Stateid = 4, go through the list and return the time when i meet a either ...
- 33
3
votes
2
answers
657
views
Rolling sum aggregates with self-referencing conditions
I have what I believe is a use-case for a recursive CTE, but I've yet to figure out how to structure it. The requirement is to create a rolling sum total over an amortization schedule, but payments ...
- 116
0
votes
2
answers
214
views
all the dependants of a stored procedure or function recursively - but shown only once
I often work with third party data professionals and they ask me to replicate whatever table(s) such and such stored procedure or function or view touches within one or more databases.
so I have ...
- 16.6k
0
votes
1
answer
42
views
Recursion only if a criteria is met
I'm using DB2 V7 and I have a two table PARTLIST and PARTTYPE as follow :
CREATE TABLE PARTLIST (PART VARCHAR(16),
SUBPART VARCHAR(16),
QUANTITY INT,
...
- 21
-1
votes
1
answer
80
views
Product part Query (BOM) [closed]
I'm using DB2 V7 and i have a product table like below :
select * from product;
Product |Component|Quantity |Line number|
--------+---------+---------+------------
Product1|Part1 |1 |1
...
- 21
0
votes
1
answer
140
views
How to have CTE query recurse parent query result one by one?
I have this query used on Postgres to return place administrative levels.
WITH RECURSIVE hierarchy(name, pinyin, level) AS (
SELECT p.name, p.pinyin, p.level, p.upper_place_object_id_fk
from ...
- 101
5
votes
1
answer
685
views
Grouping by overlapping arrays, transitively, without duplicates
I've found:
Group by array overlapping
https://stackoverflow.com/a/23650080/5419599
However, I'm having trouble putting it to use in my case.
I have a table like so (the real myid values are hashes,...
- 587
1
vote
0
answers
114
views
How to get all parents associated to a child, one row for each association [duplicate]
I have a table with two columns, Parent and Child.
i need to get an output list showing a child and all associated parents, one parent per row.
The youngest child will show as a child in rows equal ...
- 11
9
votes
2
answers
3k
views
Why doesn’t this recursive CTE with a parameter use an index when it does with a literal?
I am using a recursive CTE on a tree structure to list all the descendants of a particular node in the tree. If I write a literal node value in my WHERE clause, SQL Server seems to actually apply the ...
- 463
0
votes
1
answer
561
views
write SQL query that takes its own output as input
In a table that displays how trades are related to each other, starting from one trade, I want to find all other trades that relate to the first trade:
713613 PostPTETradeIDs 718478
713613 ...
- 101
0
votes
1
answer
638
views
Exclusion constraint on recursive foreign key (on the example of a Tree)
I have the table with recursive foreign key. It's just hierarchical tree structure:
CREATE TABLE tree (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
value ...
- 111
6
votes
1
answer
666
views
Tree Node Calculation - SQL Server
I'd appreciate some help with something I am trying to achieve please.
The parts that my company make are built from sub assemblies and sub assemblies of sub assemblies.
This looks like:
...
- 63
0
votes
0
answers
73
views
SELECT query to read recursive relationship table
I have below MySQL database table which has a recursive relationship. A hospital maybe managed by another main hospital in this scenario. I have a difficult time trying to retrieve all data with name ...
- 23
1
vote
3
answers
128
views
Count Items not In Time Range
I'm attempting to count the number of items if they are not within 30 seconds of the first item in a "group". I'm having a hard time figuring this out.
So, I have this table:
WITH ADates AS (
...
- 387
2
votes
1
answer
2k
views
CTE Query to CONCAT text (over 10,000 rows)
I am trying to construct a CTE query to loop through rows of data and concatenate text into one line for each SampleUserNumber.
Below is what I am working with:
CREATE TABLE #SampleTable
(
...
0
votes
0
answers
76
views
Cleaner approach for Recursive CTE query
I have a table called entry_changes defined as follows:
Table "public.entry_changes"
Column | Type | ...
- 436
0
votes
2
answers
2k
views
Limiting Common Table Recursion
I am trying to migrate from Oracle to MariaDB. At the moment, we are facing an issue with the following Oracle code.
SELECT id,MK, pid,AFI
FROM M A
WHERE LEVEL <= 2 -- limiting iterations ...
2
votes
1
answer
3k
views
How to query all entities associated with all children (recursive) of a node in SQL?
This question is about SQL in general. Answering specifically for MySQL would be helpful but not necessary.
Ok, I’m having trouble putting this into words… so bear with me.
Say I have a tree of ...
- 143
3
votes
2
answers
5k
views
SQL Server CTE Bottom to Top Recursive with Where clause
I have an Employee Table with an EmployeeId, ManagerId and a Name field.
The goal is to make a recursive With fetching all the rows from an employee to the top manager (ManagerId is null).
I found ...
- 133
1
vote
1
answer
504
views
Rewrite SQL query in CTE query
I have this table which I would like to store a chain of records.
CREATE TABLE table_name (
id INT,
unique_id varchar,
reference_id varchar,
);
I want to implement SQL query for MariDB ...
- 115
2
votes
0
answers
765
views
Is there a way to make postgres count planned number of rows when using recurcive CTE?
Postgres has no ideas about expected rows when using recursive CTE. Here is an expamle:
create table test_recursive (
id serial primary key,
parent_id int null references test_recursive,
name text ...
- 46
22
votes
4
answers
5k
views
How does SQL recursion actually work?
Coming to SQL from other programming languages, the structure of a recursive query looks rather odd. Walk through it step by step, and it seems to fall apart.
Consider the following simple example:
...
- 409
0
votes
1
answer
279
views
Removing duplicate-ish paths generated from recursive CTE
I have some multi-parented data, that I run a recursive CTE against to find common ancestors:
item_id |item_name |path_name |id_path_array |
--------|----------|-------------|--------------|
1 ...
- 105
3
votes
2
answers
1k
views
Joining two ltree based tree tables recursively
I have two tables, roles and permissions, both of which use ltree for maintaining a tree structure. I also have a pivot table, roles_permissions, which serves to connect the two tables. How can I join ...
- 131
9
votes
2
answers
7k
views
PostgreSQL - retrieve all IDs in a tree for a given subnode
I have a non-binary tree of customer, and I need to obtain all the IDs in a tree for the given node.
The table is very simple, just an join table with a parent id and child id.
This is a ...
- 123
2
votes
0
answers
71
views
Unrolling an entity-matching table
I have a table with the following structure:
create table pairs (
block text,
left_entity int,
right_entity int
);
That table was the result of a record linkage process. One of the ...
- 123
2
votes
1
answer
2k
views
WITH RECURSIVE with union among the recursive term
Is it possible to use WITH RECURSIVE queries in PostgreSQL where the recursive term is a union of different tables? My usecase is to extract some tuples from a table and some logical rules:
And I ...
- 123
1
vote
1
answer
2k
views
Distribute multiple payments to invoice lines
I'm having a problem allocating payments to invoice lines.
We’re using MsSql 2014
Data looks like this:
Invoice lines table (sales):
lineId invoiceId value
1 1 100
2 ...
- 11
1
vote
0
answers
260
views
Postgres recursive query not using trivial index on massive table
I've got a simple recursive query to find a tree of all posts:
with recursive all_mentions(post_id, author_id, mentioned_id) as (
select post_id, author_id, mention_id
from mentions
where ...
- 151
3
votes
2
answers
4k
views
Parent Child Relation in same Table . Select Query to get only parent records and Records not comes under parent child relation
I have a table with Parent-Child Relation in the same Table. Some Sample Data
id │ name │ parent │ is_active
════╪══════════════════════╪════════╪═══════════
1 │ Company A ...
- 31
4
votes
1
answer
2k
views
How to do hierarchical queries without CTE tables or recusive queries in Snowflake? (parent, child relationships)
I have several tables in Postgres that we are migrating to Snowflake. Some of these table are hierarchical (a foreign key point to the same table), normally to query this data I use CTE tables and/or ...
- 543
1
vote
3
answers
3k
views
SQL recursion and cte - dates timeseries
I need to build vector(s) of time series dates (say 10) for a subset of start dates.
The following statement returns 10 consecutive dates starting from '2010-01-01'.
;with cte as
(select 1 i union ...
- 11