Questions tagged [recursive]
Using recursion to solve database problems.
262 questions
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 ...
- 64.6k
-2
votes
1
answer
342
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
5k
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
144
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
442
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, ...
- 341
1
vote
3
answers
4k
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
3
votes
2
answers
151
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 ...
- 31
15
votes
2
answers
6k
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 ...
- 151
3
votes
1
answer
1k
views
Postgres plpgsql: Is a view better than a select query in a plpgsql function
I have a recursive query to the general effect of:
WITH RECURSIVE text1 (selfid, parentid, text) AS
(
SELECT DISTINCT
text.selfid,
text.parentid,
text.text
FROM
text
WHERE
text.selfid = ...
- 183
0
votes
2
answers
3k
views
Single query for retrieving a category path
I have the following table structure and content:
+-------------+-----------+------------+
| category_id | parent_id | name |
+-------------+-----------+------------+
| 1 | 0 |...
- 101
1
vote
1
answer
937
views
Remove while loop from query inserting x values
I want to make this query set based and remove the while loop for inserting.
I am passing in a TVP to the stored procedure with the type and quantity required. Because TVP needs to be READONLY, I am ...
- 121
1
vote
1
answer
99
views
Need a sum on my current column
On an Oracle database, I need to calculate something like this - on column my
id yr m v1 v2 v3 st my
1 2015 10 1 0 0 0 0
2 2015 10 0 0 2 1 4
3 2015 10 2 0 0 1 0
4 ...
- 349
0
votes
0
answers
228
views
Oracle Database - Recursive sum?
I have an Oracle db and a table and I'd want to sum the VAL column until I have the value in column SUMM 100 and the difference in column DIF - as in this example:
ID VAL SUMM DIF
1 40 40 ...
- 349
4
votes
1
answer
2k
views
Preserve order of array elements after join
I have a query that returns a CTE looking like
+-----------+-------------+
| node_id | ancestors |
|-----------+-------------|
| 1 | [] |
| 2 | [] |
| 3 |...
- 143
3
votes
1
answer
532
views
Need for recursive lookup, stuck in query design
In PostgreSQL 9.4, here is my table:
with r(pk, userid,partneruserid) as
(values
(1,1,2),
(2,1,3),
(3,1,6),
(4,2,5),
(5,2,6),
(6,3,1),
(7,4,1),
(8,5,3),
(9,6,2),
(10,6,3)
) select * from r;
In a ...
- 207
1
vote
2
answers
959
views
Include parent node ID in hierarchical recordset of child rows
I have a table of categories which are linked together by a parent_category_id column. In this query I am able to identify all of the child categories (could be several levels of children) for a ...
- 133
2
votes
1
answer
5k
views
Count children of a parent in recursive CTE
I use this query to find comments by a parent id:
WITH RECURSIVE cte (id, content, path, parent_id, depth) AS (
SELECT id,
content,
array[id] AS path,
parent_id,
...
- 167
6
votes
2
answers
4k
views
Improve performance for order by with columns from many tables
Using PostgreSQL 8.4, I'm trying to consult two tables with 1 million records using order by with indexed columns of the two tables, and I'm losing performance (with 1 column takes 30 ms and with two ...
5
votes
1
answer
3k
views
Count the nodes in a binary tree structure
I need to count the left and right nodes in a binary tree structure (output grouped by joiningDate), given a particular starting node parent id (pid).
The tree is stored in the table shown below:
...
1
vote
1
answer
2k
views
Select tree-based structure
I have a table in my SQL Server database with data like this:
ID ParentID
--- ---------
1 NULL
2 1
3 1
5 1
5 4
5 6
8 6
7 9
9 10
I need a select statement that will return all the ...
- 1,533
2
votes
2
answers
3k
views
Retrieve additional columns in recursive CTE
This works as far as getting the amount of children a "thread" has but now I can't seem to get it to pull parent row columns. Parent rows have parent_id is null, trees can be any level deep.
I manage ...
- 167
2
votes
2
answers
7k
views
How can I count all children & grandchildren until the 7th level?
I have 3 tables right now
1. members
2. members_parents
3. members_status
How can i count the children & grandchildren until to the depth level and separate the count() for the status 0 = ...
- 21
4
votes
0
answers
984
views
Recursive BOM (Bill Of Material) type query
I have what I've been told is a Bill Of Material type query - which requires a 'recursive' query. I've never heard of such a thing - and was hoping to get some help.
My schema stores asset / asset-...
- 41
2
votes
1
answer
1k
views
SQL Server : create dynamically queries to select all related data in DB based on entry table and ID
I'm tasked to create a SQL statement which will create (and run?) multiple other queries which will select all related data outgoing from an given ID (uniqueidentifier) and a given table name.
It ...
- 21
2
votes
2
answers
156
views
Recursive Manager - Oracle 10g x 11g
We have a problem adapting a recursive SQL from 11g to 10g.
Oracle 11g Query ( Working as expected )
WITH ADRECURSIVEUSERLEADER (CDLEADER,CDUSER,NMUSER,FGUSERENABLED)
AS
(
SELECT US.CDLEADER AS ...
- 53
11
votes
2
answers
3k
views
Conceptual ERD Multi-table many to many, or possibly recursive?
I'm creating a conceptual diagram [yes, I know I've included attributes and keys - but this is just for me to consolidate what I'm doing whilst learning] -- so please treat it as Conceptual with the ...
- 213
10
votes
2
answers
2k
views
Finding all joins required to programmatically join a table
Given a SourceTable and a TargetTable, I would like to programmatically create a string with all joins required.
In short, I am trying to find a way to create a string like this:
FROM SourceTable t
...
- 906
1
vote
3
answers
4k
views
Is it possible to change recursion level of a CTE in a function?
I have created a function that takes a string and it returns a table with the position and character of each letter in the string. However as CTE recursion is limited to 100 levels, it fails if a ...
- 503
4
votes
1
answer
2k
views
Enhanced ER Model Supertype / Subtype modeling issue with inheritance and recursion
Before posting, I have looked for a good solution on internet and I have coined this post for 3 days in row and trying to think through it prior this post. So if I have missed something or got it ...
- 143
2
votes
1
answer
3k
views
Finding the end of a Relationship chain optimally
I have a table that contains an id column, a parent column, and it's child column. So for every record, I know what record comes before it and what will come after it in a chain relationship, but from ...
- 503
14
votes
4
answers
73k
views
Getting all descendants of a parent
I have a table with two columns, Parent and Child.
I need to get the list of all descendants associated with the parent records.
Source Table
+----+-----------+
| Parent | Child |
+----+-----------+
...
- 261
1
vote
1
answer
470
views
Parent-child Relationship with data scattered between these records
I have a 60 column query that fills in a card on an application. The problem I am running into is gathering all the parent-child relationship data into one record per child.
As a quick example: The ...
- 167
2
votes
1
answer
3k
views
T-SQL CTE Compare previous non-NULL and current row to filter out by difference value
My sample database (in fact a CTE statement data) looks like this:
eventdate val
2012-03-23 3965
2012-03-26 3979
2012-03-27 3974
2012-03-28 3965
2012-03-29 3967
2012-03-30 3959
2012-04-02 ...
1
vote
1
answer
108
views
Finding the current prices for n Fuelstations at a certain point in time
I have a Table where price information is stored in with approx 13 million rows stored in a PostgreSQL 9.5 database.
CREATE TABLE public.de_tt_priceinfo (
id integer NOT NULL DEFAULT nextval('...
- 298
1
vote
1
answer
1k
views
Determine existence in recursive working table
I have a query that needs to check for existence of each input row type in another table, but it is unnecessary and inefficient to check for existence of subsequent rows of the same row type if one is ...
- 555
0
votes
2
answers
238
views
Hierarcy Query Mysql [duplicate]
I have a table like this...
==========================================================================
UID || PARENT ID || SPONSOR ID
===============================================...
- 101
1
vote
1
answer
371
views
What Is the Next Best Ingredient to Add?
I am building a program that allows users to optimize their grocery shopping so they can make the most recipes using the fewest ingredients.
One of the features of this program is a function I’m ...
- 11
7
votes
1
answer
2k
views
Recursive CTE to find unique slug
I have an article table where I want the slug to be unique.
CREATE TABLE article (
title char(50) NOT NULL,
slug char(50) NOT NULL
);
When the user enters a title e.g. News on Apple, I want ...
- 1,119
3
votes
0
answers
52
views
Why does retrieving fewer rows take longer? [duplicate]
I have a view that (left outer) joins multiple recursive CTEs. There are about 10k rows. The results of running SELECT * FROM MyView ORDER BY MyPrimaryKey, with statistics time and io on:
SQL Server ...
- 131
20
votes
5
answers
9k
views
How to recursively find gaps where 90 days passed, between rows
This is a kind of trivial task in my C# homeworld, but I don't yet make it in SQL and would prefer to solve it set-based (without cursors). A resultset should come from a query like this.
SELECT ...
- 213
1
vote
1
answer
773
views
hierarchical retreival of records in postgresql
I am using postgresql-9.2 . I have a table of products which stores the information about the products and its child table is content table , which contains the products linked to it.
PRODUCT MASTER ...
- 113
4
votes
1
answer
997
views
Why does Recursive CTE estimate just 1 row?
Given two cascading, self-contained (no real tables) recursive CTE's:
create view NumberSequence_0_100_View
as
with NumberSequence as
(
select 0 as Number
union all
select Number + 1
...
- 2,130
3
votes
1
answer
389
views
PL/pgSQL function or rCTE to detect depth of relations between two tables
I need to perform a PostgreSQL Function for checking the depth of relations. There is a circular binding from table a to b, back to another element in a and, in some cases back again to b. These ...
- 31
0
votes
1
answer
2k
views
Oracle Hierarchical query: with two node attributes NodeId and NodeType
I have the following use case where I want to make use of hierarchical queries to get the desired result.
In my use case I have two types of node say 'A' and 'B'. So the unique identifier of node is ...
- 103
1
vote
0
answers
268
views
How can I reduce recursion in Couchbase?
Sometimes, you need to access a certain document (A), but you do not know the key for that document (A), as that data is stored in a different document (B). So now, if you want to fetch A, it looks ...
- 121
2
votes
1
answer
4k
views
Recursive calls in stored procedure and temporary tables
I am trying to create a stored procedure in PostgreSQL which has recursive calls. The procedure creates a temporary table to store results. The temporary table isn't dropped after first call due to ...
- 33
0
votes
1
answer
16k
views
Fetch all parents from child id
I created a table with name "vegs" and inserted values just like this.
id name parent
--------------------------------------
1 fruits 0
2 Apple 1
3 ...
- 29