Questions tagged [recursive]

Using recursion to solve database problems.

25 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3
votes
0answers
687 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-...
2
votes
0answers
45 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 ...
2
votes
2answers
1k views

Parent Child Relation in same Table . Select Query to get only parent records and Records not comes under parent child relation

I have a table with Parent-Child Relation in the same Table. Some Sample Data id │ name │ parent │ is_active ════╪══════════════════════╪════════╪═══════════ 1 │ Company A ...
2
votes
1answer
2k 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 ...
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 ...
1
vote
0answers
142 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 ...
1
vote
0answers
165 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 ...
1
vote
0answers
235 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 ...
1
vote
1answer
8k 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 ...
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
0answers
251 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 ...
0
votes
1answer
95 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 ...
0
votes
0answers
21 views

Trying to write recursive query to get list of tables by foreign key

I am using this query to purge data from the bottom of the tree up. This is just the first step in my process. Does anyone have any suggestions on how to handle the recursion? Thanks DROP TABLE IF ...
0
votes
0answers
22 views

Recursive sum of children with calculated column

I'm trying to create a recursive query to determine the total cost of a multi-level bill of materials (kind of like a baking recipe list, that has sub-recipes). The top level bill of materials could ...
0
votes
1answer
18 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 ...
0
votes
1answer
41 views

How to use a recursive CTE to get ancestors in a hierarchy

create table division ( id serial primary key, name varchar not null ); -- nested sets table create table location ( id serial primary key, name varchar not null, division_id integer not ...
0
votes
1answer
33 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, ...
0
votes
1answer
33 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 ...
0
votes
0answers
37 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 ...
0
votes
0answers
49 views

Cleaner approach for Recursive CTE query

I have a table called entry_changes defined as follows: Table "public.entry_changes" Column | Type | ...
0
votes
1answer
978 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) ...
0
votes
1answer
932 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
0answers
139 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 ...
0
votes
2answers
890 views

How to get sum of last previous X days of everyday?

Let's say I have sales number of last 60 days. How would like to get sum of every last X days of everyday. For example my graph will include Sum of Day - 60 to 30 Sum of Day - 59 to 29 Sum of ...
-1
votes
2answers
33 views

Start at condition and stop when criteria is met

i'm new to MS SQL and i have to carry out the following task. What i want to output is the time of the first occurence of Stateid = 4, go through the list and return the time when i meet a either ...