Questions tagged [recursive]

Using recursion to solve database problems.

Filter by
Sorted by
Tagged with
4
votes
1answer
80 views

Determine Nodes in Network with PostgreSQL

I have a table where every entry is a node and the table contains the direct connections of each node to other nodes. I am looking to create a view with a column for each node containing all the nodes ...
1
vote
0answers
48 views

Copying a dependency structure

I'm looking for performing a copy of a "dependency structure". What I mean is: there is a tree structure representing a product and its sub components. Each of this products may have one or ...
0
votes
2answers
1k 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 ...
0
votes
1answer
1k views

How to select recursively in a child parent design situation (in MySQL)?

Let's take into consideration the following tables: CREATE TABLE actions ( id BIGINT(20) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, user_id BIGINT(20) ...
2
votes
1answer
2k views

Redshift- How to use previous row's calculations in current row

I am using redshift to determine projected inventory for next few week. I have receipts, current inventory and forecast. ei= ei(previous row) + receipts - forecast wk bi r f ei 1 100 20 ...
2
votes
2answers
43 views

How to do a recursive sum of children in mysql?

I've searched and tried for long hours, and can't seem to get what I need. I have a table of accounts as a tree -parents and children-, only leaf accounts can have transactions, any parent will NOT ...
0
votes
1answer
1k views

While Loop - Parent/Child Tree

I'm trying to recursively loop through and return all child_id's that have the root element of 9. The structure: ->9 ->->8 ->->->17 ->->22 ->->->11 Parent Child ...
1
vote
1answer
9k views

Query to List all hierarchical children with parent, grandparent etc. from entire table

I have a basic sql table with an ID and an optional parentID from the same table. This has multiple levels. I need to get a list of all IDs with each of its parents all the way to the top. if ...
2
votes
2answers
56 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 ...
5
votes
2answers
161 views

CTE query that fails to find ancestors

I have a table foo with the content So the records of the table foo can graphically be represented with this graph. When I run a procedure that should return ancestors using @starting_id=9 (...
0
votes
1answer
102 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 ...
3
votes
2answers
1k 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 ...
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
47 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
123 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 ...
10
votes
1answer
400 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 =...
2
votes
1answer
655 views

Recursive query to find shortest path in graph

I have an assignment to find the shortest possible path between two people in a table. The table is Company(company_name, job, name), all of them not null, but none of them unique (primary key is the ...
5
votes
1answer
538 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 ...
0
votes
1answer
42 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
41 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) ...
1
vote
2answers
752 views

Include parent node ID in hierarchical recordset of child rows

I have a table of categories which are linked together by a parent_category_id column. In this query I am able to identify all of the child categories (could be several levels of children) for a ...
0
votes
0answers
22 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
76 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. ...
1
vote
1answer
88 views

Finding the current prices for n Fuelstations at a certain point in time

I have a Table where price information is stored in with approx 13 million rows stored in a PostgreSQL 9.5 database. CREATE TABLE public.de_tt_priceinfo ( id integer NOT NULL DEFAULT nextval('...
0
votes
1answer
31 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
24 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
25 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 ...
12
votes
3answers
3k views

Change the system wide default for maxrecursion

How do I change the system-wide default value of MAXRECURSION? By default it is 100, but I need to increase it to something like 1000. I am unable to use query hints since I'm using a program that ...
5
votes
1answer
80 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
61 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? ...
8
votes
4answers
44k views

Getting all descendants of a parent

I have a table with two columns, Parent and Child. I need to get the list of all descendants associated with the parent records. Source Table +----+-----------+ | Parent | Child | +----+-----------+ ...
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
153 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 ...
3
votes
2answers
2k views

Table design for “Users & Groups” plus “Groups within Groups”

I need to represent users and groups, where groups can be nested within other groups. (Not my design choice – I am replicating another system and must mimic this behavior. I have no access to its ...
0
votes
1answer
48 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 ...
1
vote
1answer
30 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
2answers
142 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 ...
1
vote
3answers
350 views

SQL Server - How to solve FIFO Cost of OHB… function, cursor,?

SQL Server 2008R2 - Need to solve FIFO cost of items on hand. To do this I need to add the cost for the most recent receipts quantity up to the current on hand balance(OHB). So lets say I have ...
11
votes
1answer
19k views

CTE: Get all parents and all children in one statement

I have this working CTE example. I can select all grand-parents and all children. But how can I select all grand-parents and all children in one statement? In this example I want Grandfather, ...
3
votes
2answers
41 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 ...
1
vote
1answer
668 views

How make recursive json on mysql

my table looks like CREATE TABLE `test_comments` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `parent_id` int(10) unsigned DEFAULT NULL, `test_id` int(10) unsigned NOT NULL, `user_id` int(...
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
34 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
88 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
72 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 ...
6
votes
1answer
139 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: ...
9
votes
2answers
816 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 ...