The recursive tag has no usage guidance.
8
votes
2answers
96 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
33 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 ...
2
votes
1answer
24 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 ...
-3
votes
1answer
81 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
176 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, ...
8
votes
3answers
123 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
58 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] ...
4
votes
2answers
63 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 | ...
1
vote
2answers
56 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
124 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 ...
3
votes
1answer
89 views
Can performance be improved by breaking databases up?
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 ...
11
votes
2answers
164 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 ...
2
votes
1answer
45 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 = ...
0
votes
1answer
60 views
Single query for retrieving a category path
I have the following table structure and content:
+-------------+-----------+------------+
| category_id | parent_id | name |
+-------------+-----------+------------+
| 1 | 0 ...
0
votes
1answer
115 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 ...
0
votes
1answer
58 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 ...
0
votes
0answers
22 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 ...
3
votes
1answer
73 views
Preserve order of array elements after join
I have a query that returns a CTE looking like
+-----------+-------------+
| node_id | ancestors |
|-----------+-------------|
| 1 | [] |
| 2 | [] |
| 3 ...
3
votes
1answer
98 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 ...
1
vote
2answers
67 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 ...
2
votes
1answer
337 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,
...
5
votes
2answers
114 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 ...
4
votes
1answer
450 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:
...
0
votes
1answer
211 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
vote
2answers
127 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 ...
2
votes
2answers
518 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 = ...
2
votes
0answers
90 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 / ...
2
votes
1answer
339 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 ...
2
votes
2answers
72 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 ...
10
votes
2answers
428 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 ...
4
votes
1answer
201 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
...
1
vote
3answers
517 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 ...
3
votes
1answer
331 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 ...
2
votes
1answer
40 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 ...
-1
votes
2answers
822 views
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 |
+----+-----------+
| ...
1
vote
1answer
158 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 ...
2
votes
1answer
979 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
1answer
52 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 ...
1
vote
1answer
86 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 ...
0
votes
1answer
95 views
Hierarcy Query Mysql
I have a table like this...
==========================================================================
UID || PARENT ID || SPONSOR ID
...
0
votes
1answer
109 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 ...
3
votes
1answer
341 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 ...
3
votes
0answers
40 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 ...
14
votes
5answers
3k 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 ...
0
votes
1answer
156 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 ...
4
votes
1answer
211 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
votes
1answer
116 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 ...
0
votes
1answer
359 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 ...
1
vote
0answers
157 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 ...
1
vote
1answer
1k 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 ...
