Questions tagged [recursive]

Using recursion to solve database problems.

Filter by
Sorted by
Tagged with
1
vote
1answer
112 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
558 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
956 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
221 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
93 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
355 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
79 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
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 ...
6
votes
1answer
962 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
vote
1answer
597 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
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
800 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 ...
1
vote
1answer
8k 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
223 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
1k 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
793 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
2k 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
1answer
1k 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 ...
1
vote
1answer
1k 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 (...
4
votes
2answers
818 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-...
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
222 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
3k 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 ...
10
votes
1answer
17k 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
3k 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
870 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
170 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
6k 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
906 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
2k 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]...
4
votes
1answer
2k 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
1k 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" ...
1
vote
1answer
871 views

Is WITH RECURSIVE the right tool for my task?

Table setup: create table users(id bigserial, group_id bigint); insert into users(group_id) values (1), (1), (2), (1), (3), (3); Query: WITH RECURSIVE r AS ( SELECT users.id, users....
2
votes
1answer
6k views

Recursive cte avoiding loops

I'm trying to write a recursive CTE to explore a workflow system. Unfortunately, I'm getting maximum recursion errors due to loops: with cteActs as ( select a.id as [id], aa.TASKNEXTID as [...
3
votes
1answer
610 views

Informix - Getting all descendants of a parent

I have a table with two columns, Parent and Child. Need to get the list of all descendants associated with the parent records. Source Table: +----+-----------+ | Parent | Child | +----+-----------+ |...
5
votes
2answers
160 views

How to group all linked objects in a undirected cyclic graph

I have a table that has customers and its joint customers. e.g Customer 1 has a joint customer 2. Customer 1 is also a joint customer to Customer 3. I am trying to group all customers that are linked ...
0
votes
1answer
249 views

Using `nextval` and `currval` inside one CREATE TABLE

Let's say that we would like to model a team of people that can have subteams like so: CREATE SEQUENCE seq_teams; CREATE TABLE teams ( tid INTEGER PRIMARY KEY DEFAULT nextval('seq_teams'), ...
2
votes
1answer
22 views

need to correct this Query/Loop to find and start “new branch” in the output

given these tables: PERSONS person_id fname lname info 1 LB Johnson "info..." 2 Bobby Baker (null) 3 Malcolm Wallace (null) 4 Billy ...
3
votes
1answer
85 views

Hierarchical Storage and Query

I am editing and providing new info to this question because i've found what I need, just have a couple of questions about efficiency. Now, I have this table: category -------------------------- ...
0
votes
1answer
51 views

Doing something immediately with data in array field [closed]

I have a table that looks like this: stuff id content score children[] I'd like to run a query that selects all the children for a given id, and then right away gets the full records ...
4
votes
3answers
1k views

Best way to design / query multi-level relationship table

My company is going to develop a mobile app selling services across different regions. We are expecting users are going to hugely depend on the Search function. Thus, I need to make sure the database ...
0
votes
2answers
285 views

Calculate Value Dynamically

I need to calculate a value by using it's previous value * ColumnX Here is the formula: DividendReinvested = "Previous DividendReinvested" * (1+ValuationPercChange/100) Can I do this without a loop?...
1
vote
3answers
323 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 ...
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 ...
1
vote
1answer
782 views

How to return specific row number of a Hierachial query - Oracle

I have this query that works as expected. Within Oracle SQL Developer it returns ~19,500 rows. Unfortunately our current application limits our result to 10,000. I know I will need to run this query ...
0
votes
2answers
294 views

COUNTING results from self-referenced table recursively

I have a table called Clients. And at this table, I have an idFather to know who added this row to the table. Sample: id | user | idFather | createdAt ---+---------+----------+----------- 01 | ...
1
vote
2answers
811 views

Which columns need index in recursive table and soft delete

I have a Category table like below : I have a recursive relationship on this Category table. I use soft delete in my project. I have 2000 rows in Category table. When I want to show data in a UI ...
16
votes
2answers
9k views

Recursive CTE to find Total for all children

Here is an assembly tree that I want to search using a recursive T-SQL Query (presumably CTE) with the expected results below. I want to know the total amount per assembly given any part. Meaning if ...
1
vote
1answer
1k views

mysql query parent child relation get childs

I have a user table in mysql. per user has got a parentuserID in the same table. i want to crate a function that give me the all child users when i give the UserID. my table structure is below. DROP ...