Questions tagged [recursive]

Using recursion to solve database problems.

Filter by
Sorted by
Tagged with
2
votes
1answer
329 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
2answers
2k views

Single query for retrieving a category path

I have the following table structure and content: +-------------+-----------+------------+ | category_id | parent_id | name | +-------------+-----------+------------+ | 1 | 0 |...
1
vote
1answer
766 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 ...
1
vote
1answer
88 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
105 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
583 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
440 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
705 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
3k 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, ...
6
votes
2answers
2k 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
1answer
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: ...
0
votes
1answer
1k 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
2k 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
6k 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 = ...
3
votes
0answers
626 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
1answer
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 ...
2
votes
2answers
135 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 ...
11
votes
2answers
1k 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 ...
8
votes
2answers
1k 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
2k 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
1k 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
1k 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 ...
6
votes
4answers
35k 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 | +----+-----------+ ...
1
vote
1answer
446 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
2k 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
81 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('...
1
vote
1answer
501 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
2answers
135 views

Hierarcy Query Mysql [duplicate]

I have a table like this... ========================================================================== UID || PARENT ID || SPONSOR ID ===============================================...
1
vote
1answer
324 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 ...
7
votes
1answer
1k 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
50 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 ...
17
votes
5answers
7k 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
641 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
644 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 ...
3
votes
1answer
189 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
1k 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
247 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 ...
2
votes
1answer
3k 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 ...
0
votes
1answer
10k 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 ...
2
votes
1answer
2k views

Intentionally recursive instead of insert trigger does not want to nest

I have been struggling to get an instead of trigger to recursively call itself. The idea is that when a row gets inserted into my table the trigger is placed on, based upon certain conditions ...
6
votes
2answers
2k views

Query to find circular references

I have two tables ID Task 1 1 2 2 3 3 4 4 Col1 depend 2 3 2 4 3 1 4 2 ID and Col1 are related through FK constraint. I want to find out all circular references. Here ID ...
12
votes
1answer
9k views

How do I sort the results of a recursive query in an expanded tree-like fashion?

Let's assume you have a nodes tables like this: CREATE TABLE nodes ( node serial PRIMARY KEY, parent integer NULL REFERENCES nodes(node), ts timestamp NOT NULL DEFAULT now() ); It ...
4
votes
1answer
4k views

Making a recursive CTE like query, but for many records

I have a table with a hierarchy ( with a recursive parentID column), for one record, I know how to get the last parent record, with the following code : declare @Id integer ;WITH CTE AS ( SELECT ...
1
vote
1answer
2k views

Self-join unique pairs

I have a table (players) containing a list of players, I would like to pair up these players in a unique way, so that every player's opponent is unique every round (ie. every time the SELECT query is ...
2
votes
2answers
393 views

SQL Server - Recursive “find all FK connections for ID through the entire DB” query

I'm dealing with a DB with hundreds of tables, with interconnecting FK references producing potentially thousands of different permutations for how for example table A and table K can join together. ...
2
votes
1answer
12k views

Recursive Query in MySQL using stored proceedure and CURSOR

I'm expanding our user/group system to allow for dynamic groups which are made up of other groups members. I have three tables users, groups, and relationships. For simplicity sake, lets say users ...
0
votes
1answer
877 views

Flatten recursive hierarchy

I have the following recursive query: SELECT SYS_CONNECT_BY_PATH(object_name, '\') AS path FROM user_group_nodes START WITH parent_id = '2892107544AA2278166C1AA8D123E761' CONNECT BY parent_id = PRIOR ...
2
votes
1answer
2k views

Tree structure hierarchical query sorting

I have the following structure of table. CREATE TABLE table1 ( serial_num integer NOT NULL, parent_num integer, parent_key text, key_ary_num integer[], level integer, rank integer, ...
2
votes
1answer
145 views

How do I query a table with many trees of data, for one tree?

I have a the NAICS schema loaded and all is working well. However I'm confused as how I'd query it in Postgrseql? The table looks something like this, =========NAICS_2012========= naics | ...
8
votes
1answer
4k views

Recursive CTE performance

Need help with recursive CTE performance. Below CTE is running very slow as it is trying to pull heirarchical data recusively. Table is big with every root id having upto 3 recursive itemid. There ...