Questions tagged [recursive]
Using recursion to solve database problems.
53 questions with no upvoted or accepted answers
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
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
2
votes
0
answers
23
views
Find best intersect (most elements) between Order (haystack) and Pricerules (needle)
Full disclosure
I have posted a greater scoped question at SO a couple of days ago with a bounty (no answers yet).
Scope here
How can a CTE be used to find a match with the most elements?
For the ...
- 219
2
votes
0
answers
180
views
Problem with sysdepends while checking dependencies
I have this script to list all dependencies of all views in a database . I get the dependencies in the right order when I use sysdepends ,but I am missing a few dependencies while using sysdepends. ...
- 99
2
votes
2
answers
3k
views
Optimizing a recursive CTE or replacing it with a temporary table
I have a recursive query like this:
with recursive PCte(id) as
(
select p1.id from Product p1 where p1.parent_id is null and p1.serial_number in
('123', '124','125', 'n')
union all
...
2
votes
0
answers
483
views
Drop intermediate results of recursive query in Postgres
I'm trying to aggregate the contents of a column from a directed acyclic graph (DAG) stored in a Postgres table.
Each dag row has an id, bytes, and may also have a parent referencing another row. I'm ...
- 155
2
votes
1
answer
124
views
Org chart CTE with exact index but plan shows expensive sort node
I've got a moderately large table (148 million rows) that's got pretty common org chart attributes to it. Names are changed, but it's the same idea. I was thinking of adding a hierarchyid column to ...
- 255
2
votes
0
answers
765
views
Is there a way to make postgres count planned number of rows when using recurcive CTE?
Postgres has no ideas about expected rows when using recursive CTE. Here is an expamle:
create table test_recursive (
id serial primary key,
parent_id int null references test_recursive,
name text ...
- 46
2
votes
0
answers
71
views
Unrolling an entity-matching table
I have a table with the following structure:
create table pairs (
block text,
left_entity int,
right_entity int
);
That table was the result of a record linkage process. One of the ...
- 123
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
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
1
vote
2
answers
46
views
How to convert a recursive query to a function?
I have written a PostgreSQL query that's working perfectly:
WITH RECURSIVE x AS (
SELECT i, parent, id, name, type, '' AS path
FROM entry
WHERE name = 'JS-VBNET-2'
UNION ALL
SELECT e.i,...
1
vote
0
answers
318
views
Is it possible to limit infinte recursion in an Oracle CTE?
In a DBMS like Microsoft SQL, there is a default limit of 100 recursions, in a recursive CTE, which can be adjusted with:
WITH cte AS (
…
)
SELECT * FROM cte OPTION (MAXRECURSION …);
As far as I ...
- 2,919
1
vote
0
answers
126
views
Mysql8 Unable to order the result of WITH RECURSIVE query
I am using the WITH RECURSIVE query to generate a hierarchy of users. The data and the query used is shared in https://www.db-fiddle.com/f/oTbCcMQb5a4B6V96DAYJHG/0 . The query is returning the results ...
- 121
1
vote
0
answers
212
views
How to write query to link complex Parent-Child relationships
We have a table that has a DefectID field, and a PreviousDefectID field, in order to link defect records to each other as they change through time.
I need to link these fields together by creating a ...
- 11
1
vote
0
answers
50
views
Recursive CTE get
I have the following tables:
Table1
id
entity_id
value
Table2 - Same structure as Table1
id
entity_id
value
Table3
source_id
destination_id
entity_id
Table1 and Table2 are both partitions from ...
- 11
1
vote
0
answers
62
views
TSQL - retrieve all IDs in a tree for a given subnode
I've tried to translate and implement the PostGres solution to the problem found here: PostgreSQL - retrieve all IDs in a tree for a given subnode, but I'm either programming it wrong, or ...
- 111
1
vote
0
answers
121
views
How to keep up-to-date sums in the tree?
I have a tree table. I need to ensure that when inserting or updating a record, the sumvalue field of all parents is updated - this is the sum of all child elements. I wrote a trigger (even a query is ...
- 119
1
vote
0
answers
76
views
Splitting time period into multiple with irregular length, grouped by name
We have a set of data containing event dates, other event data and the start and end date that of the 'event season' that the event took place in.
The issue is as follows: In postgresql, I wish to ...
- 123
1
vote
0
answers
80
views
Copying a dependency structure
I'm looking for performing a copy of a "dependency structure". What I mean is: there is a tree structure representing a product and its sub components. Each of this products may have one or ...
- 11
1
vote
0
answers
260
views
Postgres recursive query not using trivial index on massive table
I've got a simple recursive query to find a tree of all posts:
with recursive all_mentions(post_id, author_id, mentioned_id) as (
select post_id, author_id, mention_id
from mentions
where ...
- 151
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
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
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
0
votes
1
answer
16
views
How to fuzzy query a directory structure in PostgreSQL?
I was able to put together a rough idea for a PostgreSQL query to query over a node table, which contains id, parent__id, slug, and file_url (optional). It's considered a file if it has file_url, ...
- 221
0
votes
1
answer
61
views
PostgreSQL CTE Recursion - How to Improve Performance for Parent->Child Tree (8 levels deep, 890 roots)
I am working on a query that performs PostgreSQL CTE Recursion in PostgreSQL 13. This query is intended to recurse from the root nodes to the deepest leaf node with certain properties (labeled 'begat'...
- 50
0
votes
1
answer
36
views
Designing a data model to handle both recurrence and nested dependencies
I'm building a web application that involves both recurrence and nested dependencies, and I'm struggling to pin down the appropriate data model.
The problem that the application aims to solve is: we ...
- 101
0
votes
1
answer
79
views
When generating combinations using a CTE, is there a way to use an index to group the elements of each combination?
I have a project that involves combining a variety of things into their possible combinations. This includes permutations, combinations, with and without repetition, and some more exotic ways of ...
- 439
0
votes
0
answers
171
views
Recursive CTE hiearchy snowflake -- join/ expand outward (columns) instead of rows?
I'm doing a dependency mapping explosion (like parts explosion).
I did a typical recursive CTE with a union all.
It looks like
with CTE as
( select abc from myTable where start_point = X
union all
...
- 1,709
0
votes
0
answers
35
views
How to match rows from relationship via a recursive CTE
Apologies for the terrible question title, I couldn't think how to describe this question in a single line.
I have a schema that looks roughly like this: https://dbdiagram.io/d/...
- 163
0
votes
1
answer
241
views
"ON UPDATE" in self referencing table
I have a table Category with a parent_id column referencing the self id column, for this, I used "ON DELETE CASCADE" meaning if the parent is deleted, other children would be deleted too.
At ...
- 23
0
votes
0
answers
3k
views
How to fix error "Line 25: The statement terminated. The maximum recursion 100 has been exhausted before statement completion."
I added OPTION (MAXRECURSION 1000) at the very bottom but it was not enough. My query:
Use DatabaseName;
DECLARE @SOURCE_TABLE_NAME NVARCHAR(200) = 'WorkOrderVersion'
DECLARE @SOURCE_SCHEMA_NAME ...
- 1
0
votes
1
answer
821
views
MySQL Select query for a calculated column that uses previous row value for current row
I am stuck at this. Trying to help PM team to translate a calculation from Excel using a MySQL select query to do reporting. Your help is appreciated.
Table has 3 columns - Employee, Month, Salary. ...
- 1
0
votes
0
answers
225
views
Make this recursive CTE more dynamic
I have this script that enables me to extract hierarchies and the description for each level however the number of levels has to be known beforehand to define the number of select queries necessary ...
- 1
0
votes
0
answers
235
views
Optimising a recursive SQL query that processes several million records in BQ
I need help optimizing a recursive SQL query in BQ.
I have hierarchical data stored in a table as parent-child relationships, i.e
will be stored as
parent_item_id
child_item_id
1
1
1
2
1
3
3
4
...
- 101
0
votes
0
answers
173
views
How to use variable in the select query PostgreSQL
I'm looking for a way to write a recursive query, but I'm getting an error.
"SQL Error [42601]: ERROR: syntax error at or near "with recursive"
Position: 287"
query result "...
- 1
0
votes
1
answer
278
views
Retrieve all linked categories
I have Categories stored in a single table.
Where there is no limit on number of childerns.
I want to fetch all the linked categories for the provided category id:
The reason for getting the hierarchy ...
- 103
0
votes
0
answers
384
views
How to optimize (or get an alternative faster solution to) this recursive query that groups using recursive CTEs in Postgres?
I am on the quest to implement an NMS filter for labels in a Database.
For context, here's a link to my first question, where I explain what NMS is and what I'm trying to achieve (I will edit that ...
- 51
0
votes
0
answers
214
views
Mysql parent child to pivot table
I want to convert my parent child table to pivot,below is my query
CREATE TABLE parent_child
with recursive cte (id, name, parentid,level) as (
select id,
name,
...
0
votes
0
answers
1k
views
SQL (postgres) - Recursive view for all parents of a child
Solved: Solution from crossposting: https://stackoverflow.com/a/71149082/8082262
I have two tables: community2collection(collection_id,community_id) and community2community(child_comm_id,...
0
votes
1
answer
532
views
Can EXPLAIN be used to get some insights about Common Table Expression (CTE)?
I need to find the ancestors in a table having an ID value and a Google research point me to recursive Common Table Expression (CTE) I wonder if EXPLAIN can be used to get some insights on how MySQL ...
- 1,888
0
votes
1
answer
79
views
How can I include an entry for an intermediate row In a recursive query summing up children
I have a schema with activity(activity_id, parent_activity_id, name) and period(activity_id, duration, etc).
An activity can have ZERO to many associated periods. Further, activities have a reference ...
- 121
0
votes
0
answers
31
views
Fastest way of updating a spider web of data
I have a large table with over 60 million rows, which has relational data inside of it. I need to update each of these rows to the newest date, of the newest member, including members that that ...
- 1
0
votes
1
answer
36
views
Getting Indirect parents
I have following different tables
LeafChilds
ChildId, ChildName
1, Child1
2, Child2
Group
GroupId, GroupName
1, Group1
2, Group2
3, All
GroupChildMapping
GroupId, ChildId
1, 1
2, 1
2, 2
...
- 9
0
votes
1
answer
146
views
calculate or get price of parent items on bill of materials
i want to calculate or get the price of parent items on bill of materials query
here is the fiddle-> https://www.db-fiddle.com/f/o3jLgZxKNLG14mna8QGdVN/6
please note in the second row, betax has ...
- 9
0
votes
1
answer
224
views
Insert dummy data based on data time found in column
while doing data collection, I've come to realize that some of the data are missing and could not be re-collected due to some issue with the program. One of the suggestions given was to replace the ...
- 13
0
votes
1
answer
42
views
Recursion only if a criteria is met
I'm using DB2 V7 and I have a two table PARTLIST and PARTTYPE as follow :
CREATE TABLE PARTLIST (PART VARCHAR(16),
SUBPART VARCHAR(16),
QUANTITY INT,
...
- 21
0
votes
1
answer
140
views
How to have CTE query recurse parent query result one by one?
I have this query used on Postgres to return place administrative levels.
WITH RECURSIVE hierarchy(name, pinyin, level) AS (
SELECT p.name, p.pinyin, p.level, p.upper_place_object_id_fk
from ...
- 101
0
votes
0
answers
73
views
SELECT query to read recursive relationship table
I have below MySQL database table which has a recursive relationship. A hospital maybe managed by another main hospital in this scenario. I have a difficult time trying to retrieve all data with name ...
- 23