Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [recursive]

Using recursion to solve database problems.

0
votes
1answer
17 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
29 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
122 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
37 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
33 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
67 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 ...
18
votes
4answers
1k 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
25 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
62 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
241 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 ...
1
vote
0answers
34 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
43 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
118 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 ...
1
vote
0answers
40 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 ...
2
votes
1answer
281 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 ...
3
votes
0answers
227 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 ...
1
vote
3answers
103 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 ...
1
vote
1answer
43 views

Sql query for all ascendent of a node

I have a table having records as below: ID Name parentId 1 A Null 2 B 1 3 C Null 4 D 3 5 E 4 I need out put in following format ID Name ParentName ...
0
votes
1answer
165 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
1answer
466 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
0answers
94 views

How to get the hierarchy of particular employee using JPA spring boot?

I am trying to find a way to get the employee hierarchy in an organization using JPA and Spring boot. The problem is similar to the following link Similar Data Model If A reports to B and B reports ...
1
vote
1answer
54 views

How to link rows in a many-to-many relationship where types have parents, grandparents etc

In PostgreSQL 10.4, I have a model/table category with a Foreign Key to itself. The hierarchy can extend to any number of levels: parent, grandparent etc. I have a model/table product. Each product ...
0
votes
1answer
111 views

Geting rows from self-referencing single table using Postgres

I am trying to get rows from self-referencing single table in Postgres. I got table A and I would like to get Table B. For given ProcID using Input_proc_Id I want to get another row until i get ...
0
votes
2answers
43 views

Select starting node ID in CTE

I have following table: create table osoba( id int primary key, idNd int, nazwim nvarchar(500), ) Data looks like below: id idNd nazwim 1 NULL grandpa 2 1 child1 3 1 child2 4 2 ...
2
votes
1answer
576 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 ...
6
votes
1answer
229 views

Help with CTE Aggregating Children Recursively

I'm trying to create a query to calculate the "Available to Sell" qty of all items in inventory. In this case, an item can have current available inventory, but could also be a kit, which is comprised ...
-1
votes
1answer
269 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
556 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 ...
3
votes
1answer
352 views

Find X parents with recursive postgres

Have issue with building a recursive query in Postgres. Tried with this one but it doesn't seem to work. So table user have user_id and referred_by_user_id. referred_by_user_id can be empty, if it is ...
0
votes
1answer
3k 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 ...
5
votes
1answer
191 views

Why does this CTE return the bulk of its results immediately, but then take several minutes to finish?

I've got a schema roughly like this (this is a simplification of my actual schema): CREATE TABLE foo ( key1 NUMERIC(6) NOT NULL, key2 VARCHAR(32) NOT NULL, val VARCHAR(255) NULL, ...
2
votes
1answer
664 views

Recursive self relational via join column

Having this specific simple many to many self referential structure An item owns other items through the joins table; trying to retrieve the whole tree structure as json while querying a given ite. ...
12
votes
1answer
569 views

How to filter out Scalar Valued User-Defined Function usage from SQL Server Audit Data?

We have a SQL Server database which has a database audit specification which audits all execute actions on the database. CREATE DATABASE AUDIT SPECIFICATION [dbAudit] FOR SERVER AUDIT [servAudit] ADD ...
0
votes
2answers
1k views

Recursive relationship database [closed]

So I have an university project that has to implement some kind of a recursive relationship like (for example - employee may be managed by other employee(https://www.sqa.org.uk/e-learning/...
1
vote
1answer
52 views

Conditional Query [closed]

can someone please help me with the below requirement? I have a table with sample data below Now, I want to write a query that 1. It don't miss any ID in the report 2. If a particular ID has a ...
3
votes
2answers
573 views

Help on Self-Referential CTE SQL SERVER

I have this table below which is has a hierarchy to it where the number of levels vary, for this example I chose a subset with 7 levels. L_Key Parent Level P_Key ------------------------- 1393 ...
0
votes
1answer
642 views

How to use WITH RECURSIVE with values in an array?

I'm sure I'm missing something simple here, but I'm trying to use PostgreSQL's WITH RECURSIVE to create a graph of children records. The data (simplified) looks like this: CREATE TABLE offspring (...
3
votes
1answer
352 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-...
2
votes
2answers
379 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
153 views

Recursive Seller Account with Balance

I need to build a database for managing Short Messaging Service (SMS). In this I have 2 types of users Reseller Must have starting SMS balance for example: 1,000 - credit table He can sell ...
3
votes
2answers
2k views

Get Nested/Hierarchical Data from SQL Table

I have a table relationship between 'Tag' and 'Task'. It is M:M. The relationship is mapped in the table 'TagTaskMapping'. The TagAssignment table stores the relationship between a Tag and a date. So ...
5
votes
1answer
10k 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, ...
0
votes
4answers
2k views

Finding the first parent (manager) of an employee that makes twice as much in a self-referencing table?

Example you have the following table CREATE TABLE emp (id,name,salary,manager) AS VALUES ( 1 , 'james' , 10000 , null ), ( 2 , 'alex' , 5000 , 1 ), ( 3 , 'Alice' , 4500 , 1 ), ...
0
votes
1answer
577 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
1answer
92 views

Binary tree “get open list” SQL query

I have a node table that looks like this, plus it has an inserted_at column: id | parent_id ----------+----------- 1 | nil 2 | 1 3 | 1 4 | 2 5 | 2 ...
0
votes
1answer
3k views

Recurstive query parent child speed

Recursive queries to find the tree-structure (parent-child) in a table are commonly found here, however most have the problem that the childID must be higher than the parentID. A possible solution ...
-1
votes
3answers
480 views

Create parent node and parent id [closed]

I have a table which contains id, nodeid, nodevalue, and nodelevel columns. Now I want to create new temp column ParentnodeId. I tried using CTE but not getting the correct result. Id Nodeid ...
1
vote
1answer
1k views

How do I ORDER BY timestamp in this recursive query?

I have a working reqursive query for my comments table. WITH RECURSIVE comments_tree( id, content, created_at, path, level ) AS ( SELECT id, content, created_at, ARRAY[id]...
3
votes
1answer
1k views

PostgresSQL recursive query running slow with low load and almost no data

I'm building a simple ACL system and the datastore is Postgres 9.6. Permissions are assigned to groups, which can have subgroups, and people are assigned to groups. I have a poor performing query ...
1
vote
0answers
863 views

Joining recursive table in PostgreSQL

Situation I have two simple tables, one called "node_relations" and one called "node_information". The table "node_relations" represents a simple parent-child hierarchy, having only two columns "id" ...