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
656 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
1answer
976 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
114 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
153 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
206 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
571 views

Recursive query to find shortest path in graph

I have an assignment to find the shortest possible path between two people in a table. The table is Company(company_name, job, name), all of them not null, but none of them unique (primary key is the ...
1
vote
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 ...
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
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
32 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
31 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
20 views

Modeling tabular structure in MSSQL to store data like Excel sheet applying Column inheritance

I've created this database structure to store tabular information (it's a simplification of my real structure but enough to show the point). In it I have Sheets and each Sheet can define different ...
0
votes
0answers
31 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
44 views

Parent/child table with include/exclude items

I have a table with parent-child relations. The relations can go n-level deep. There is also a table with elements that belong to a group. CREATE TABLE group_children( id serial PRIMARY KEY, ...
0
votes
0answers
45 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
908 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
837 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
118 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
618 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
32 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 ...