Questions tagged [recursive]
Using recursion to solve database problems.
253
questions
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
2
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 ...
- 121
5
votes
1
answer
299
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
4
votes
2
answers
7k
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 ...
- 169
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 ...
- 753
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
63
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
9
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,425
4
votes
2
answers
4k
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 ...
- 10.6k
0
votes
1
answer
364
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,394
17
votes
1
answer
35k
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,481
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
1
answer
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
264
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
11k
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" ...
- 19
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,453
4
votes
2
answers
937
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
244
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
439
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
27
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
97
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
55
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
299
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,394
1
vote
3
answers
610
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
13k
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
1k
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
787
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
1k
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
14k
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
1
vote
1
answer
2k
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 ...
3
votes
1
answer
422
views
Limited utility in RECURSIVE VIEWS?
In the docs for recursive queries with WITH, you'll find this
Recursive queries are typically used to deal with hierarchical or tree-structured data.
This seems to be true, but how does this ...
- 62.1k
-2
votes
1
answer
311
views
MySQL - GROUP BY clause recursively? [closed]
Under MySQL I can not group the three separate data columns via GROUP BY clause.
This sample contains four brands, each brand has 2 models and 4 versions of engines. I need to extract 4 vehicles the ...
- 29
7
votes
2
answers
4k
views
Looking for a simpler alternative to a recursive query
The actual query is more involved, but the problem I'm facing can be distilled to this:
A query to filter a rowset of monotonically increasing integers so that - in the final result set, row(n+1)....
- 71
12
votes
3
answers
4k
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 ...
- 229
4
votes
1
answer
141
views
How to get all connected values as a single column
I'm trying to use CTE recursion.
This is my scenario SQL Fiddle.
It is a simple tree structure:
CREATE TABLE [dbo].[AL](
[IdAL] [int] IDENTITY(1,1) NOT NULL,
[ID1] [int] NOT NULL,
[ID2] [...
- 41
4
votes
2
answers
402
views
Finding earliest connected value over two columns
I have a, perhaps slightly esoteric, use case.
Given a table like the following:
id1 | id2 | timestamp
-----+-----+---------------------
3 | 4 | 2016-03-22 09:52:15
1 | 2 | ...
3
votes
2
answers
3k
views
PostgreSQL: Calculate balance working backwards from current balance
I have a balances table that stores the current_balance for bank accounts; and a transfers table that contains a row for every transfer to- and from an account (deposits are positive numbers, ...
- 331
1
vote
3
answers
3k
views
How to draw ER diagram for recursive relation when the number of roles is more than 3?
I know for a recursive relation where there are 2 roles, for example:
Worker works for Manager
and Worker and Manager both are 2 roles of Employee, we show it this way:
But what if Employee had roles ...
- 11