Questions tagged [recursive]

Using recursion to solve database problems.

Filter by
Sorted by
Tagged with
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
652 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
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 ...
5
votes
1answer
217 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
724 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 (...
3
votes
2answers
636 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
212 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 ...
7
votes
1answer
15k 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
778 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
159 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
5k 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
806 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
756 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
5k 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
568 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
152 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
238 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
84 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
956 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
272 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
303 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 ...
1
vote
1answer
8k 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
731 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
212 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
747 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
8k 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 ...
3
votes
1answer
392 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 ...
-2
votes
1answer
139 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 ...
7
votes
2answers
962 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)....
10
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
125 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] [...
3
votes
2answers
277 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
2answers
1k 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, ...
1
vote
3answers
2k 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 ...
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 ...
15
votes
2answers
3k views

PostgreSQL Recursive Descendant Depth

I need to calculate the depth of a descendant from it's ancestor. When a record has object_id = parent_id = ancestor_id, it is considered a root node (the ancestor). I have been trying to get a WITH ...