Questions tagged [recursive]

Using recursion to solve database problems.

Filter by
Sorted by
Tagged with
0
votes
0answers
15 views

Query to find hierarchical relationship between objects [duplicate]

I want to execute a SQL query in PostgreSQL 10.6. I have two tables, A and B, that contain a common field, which contains a code. Each code has an inherited code and I want to build a hierarchical ...
0
votes
1answer
46 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 ...
2
votes
1answer
84 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 ...
0
votes
1answer
92 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
votes
1answer
367 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 =...
0
votes
1answer
29 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 ...
1
vote
1answer
38 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) ...
0
votes
0answers
21 views

Trying to write recursive query to get list of tables by foreign key

I am using this query to purge data from the bottom of the tree up. This is just the first step in my process. Does anyone have any suggestions on how to handle the recursion? Thanks DROP TABLE IF ...
3
votes
1answer
69 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. ...
0
votes
1answer
28 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
0answers
22 views

Recursive sum of children with calculated column

I'm trying to create a recursive query to determine the total cost of a multi-level bill of materials (kind of like a baking recipe list, that has sub-recipes). The top level bill of materials could ...
0
votes
1answer
23 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 ...
4
votes
1answer
66 views

Recursivly get a Tree Via self joined table

Using other questions here & 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 ...
0
votes
1answer
38 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? ...
0
votes
1answer
18 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 ...
1
vote
1answer
108 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 ...
4
votes
1answer
369 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 ...
1
vote
1answer
29 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 | ...
0
votes
1answer
41 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 ...
0
votes
2answers
102 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 ...
-1
votes
2answers
33 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 ...
3
votes
2answers
37 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 ...
0
votes
2answers
72 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 ...
0
votes
1answer
33 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, ...
-1
votes
1answer
30 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 ...
0
votes
1answer
33 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 ...
5
votes
1answer
86 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,...
1
vote
0answers
70 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 ...
9
votes
2answers
721 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 ...
0
votes
1answer
54 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 ...
0
votes
0answers
22 views

Modeling tabular structure in MSSQL to store data like Excel sheet applying Column inheritance

I've created this database structure to store tabular information (it's a simplification of my real structure but enough to show the point). In it I have Sheets and each Sheet can define different ...
0
votes
1answer
130 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 ...
6
votes
1answer
138 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: ...
0
votes
0answers
37 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 ...
1
vote
3answers
113 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 ( ...
2
votes
1answer
307 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
0answers
49 views

Cleaner approach for Recursive CTE query

I have a table called entry_changes defined as follows: Table "public.entry_changes" Column | Type | ...
0
votes
1answer
363 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
1answer
712 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 ...
3
votes
3answers
1k 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 ...
1
vote
1answer
189 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 ...
1
vote
0answers
142 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 ...
0
votes
2answers
884 views

How to get sum of last previous X days of everyday?

Let's say I have sales number of last 60 days. How would like to get sum of every last X days of everyday. For example my graph will include Sum of Day - 60 to 30 Sum of Day - 59 to 29 Sum of ...
19
votes
4answers
4k 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: ...
0
votes
1answer
34 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 ...
1
vote
2answers
286 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 ...
5
votes
2answers
2k 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 ...
2
votes
0answers
45 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 ...
2
votes
1answer
170 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 ...
1
vote
1answer
788 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 ...