Questions tagged [recursive]

Using recursion to solve database problems.

Filter by
Sorted by
Tagged with
12
votes
3answers
2k 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 ...
4
votes
1answer
53 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
17 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? ...
6
votes
4answers
37k 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 | +----+-----------+ ...
2
votes
1answer
893 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
1answer
17 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 ...
0
votes
2answers
528 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 ...
1
vote
1answer
546 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 ...
1
vote
1answer
69 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
861 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) ...
1
vote
1answer
38 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
1k 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
7k 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
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 ...
0
votes
1answer
29 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
25 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
801 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 ...
0
votes
2answers
65 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
32 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
309 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 ...
8
votes
1answer
16k 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
33 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
1answer
472 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
71 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
31 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
28 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
85 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 ...
6
votes
1answer
125 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: ...
8
votes
2answers
504 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 ...
3
votes
1answer
510 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 ...
0
votes
1answer
37 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 ...
3
votes
2answers
144 views

Can performance be improved by breaking databases up? [closed]

Performance is my over-riding concern, as I have a heavily recursive CTE that is the engine room of my application. This CTE may have to work on 4 columns of up to 7 million rows. Queries can take ...
0
votes
0answers
18 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
87 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 ...
4
votes
2answers
688 views

self-joins in recursive CTE: `recursive reference must not appear more than once`?

This is tested with PostgreSQL 9.6, but it's a general SQL question. Is it possible to use self-joins of the recursive table in a recursive CTE (rCTE)? I tried the following rCTE containing a self-...
0
votes
1answer
260 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 ...
0
votes
2answers
135 views

Hierarcy Query Mysql [duplicate]

I have a table like this... ========================================================================== UID || PARENT ID || SPONSOR ID ===============================================...
0
votes
0answers
26 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
112 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
185 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
42 views

Parent/child table with include/exclude items

I have a table with parent-child relations. The relations can go n-level deep. There is also a table with elements that belong to a group. CREATE TABLE group_children( id serial PRIMARY KEY, ...
0
votes
0answers
45 views

Cleaner approach for Recursive CTE query

I have a table called entry_changes defined as follows: Table "public.entry_changes" Column | Type | ...
1
vote
1answer
1k views

Tables with mixed ON DELETE CASCADE and ON DELETE RESTRICT, rule set with recursion?

Not sure if this is a generic question, but unless not, this is MySQL 5.6. We have a database consisting of ~300 tables, INNODB, all connected with proper foreign keys. One of these tables can be ...
2
votes
1answer
9k views

Select all children down a hierarchy

I have a table of hierarchical data like this: BookGroupID GroupName ParentGroupID ----------- --------- ------------- 0 Primary 0 1 abc 0 2 abd ...
2
votes
1answer
517 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 ...
19
votes
4answers
3k 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: ...
1
vote
1answer
157 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 ...