Questions tagged [recursive]
Using recursion to solve database problems.
262 questions
2
votes
1
answer
326
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
...
- 23
2
votes
1
answer
2k
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(...
- 21
1
vote
1
answer
2k
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) ...
- 293
1
vote
0
answers
561
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 ...
- 11
1
vote
1
answer
543
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 ...
- 141
2
votes
1
answer
1k
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 ...
- 160
0
votes
2
answers
176
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
1
answer
3k
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 ...
- 21
6
votes
1
answer
2k
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 ...
- 525
2
votes
1
answer
1k
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 ...
- 31
1
vote
1
answer
2k
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 ...
- 21
2
votes
1
answer
2k
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 ...
- 133
3
votes
1
answer
13k
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 ...
- 131
5
votes
1
answer
324
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,
...
- 163
5
votes
2
answers
8k
views
Create JSON object from recursive tree structure
Having this simple many-to-many self-referential structure.
An item owns other items through the joins table:
CREATE TABLE items (
item_id serial PRIMARY KEY
, title text
);
CREATE TABLE ...
- 179
12
votes
1
answer
1k
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 ...
- 783
0
votes
2
answers
3k
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
1
answer
64
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 ...
- 11
3
votes
1
answer
3k
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 ...
- 53
1
vote
1
answer
3k
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 (...
- 21
10
votes
2
answers
3k
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,692
4
votes
2
answers
5k
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 ...
- 11k
0
votes
1
answer
373
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 ...
- 101
3
votes
2
answers
4k
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 ...
- 1,414
20
votes
1
answer
38k
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, Father, ...
- 1,561
0
votes
5
answers
4k
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
2
answers
2k
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 ...
- 101
0
votes
1
answer
274
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
...
- 103
0
votes
1
answer
12k
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 ...
- 167
1
vote
1
answer
2k
views
Social network comments and posts model in PostgreSQL 9.6
I'm trying to figure out a data structure for migrating from MongoDB to PostgreSQL. Within my posts table in Postgres, I have a JSONB[] array holding comments for the post.
The thing is that each ...
Duxducis
-1
votes
3
answers
2k
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 ...
- 3
1
vote
1
answer
3k
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]...
- 113
4
votes
1
answer
4k
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 ...
- 143
1
vote
0
answers
2k
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" ...
- 119
1
vote
1
answer
2k
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....
- 11
4
votes
1
answer
11k
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 [...
- 1,463
4
votes
2
answers
1k
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 |
+----+-----------+
|...
- 43
5
votes
2
answers
263
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 ...
- 213
0
votes
1
answer
488
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'),
...
- 145
2
votes
1
answer
28
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 ...
- 81
3
votes
1
answer
98
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
--------------------------
...
- 81
0
votes
1
answer
56
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
3
answers
2k
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 ...
- 41
0
votes
2
answers
311
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,414
1
vote
3
answers
646
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
...
- 111
2
votes
1
answer
14k
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
1
answer
2k
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 ...
- 11
0
votes
2
answers
859
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 | ...
- 241
1
vote
2
answers
2k
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 ...
- 121
17
votes
2
answers
15k
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 ...
- 314