All Questions

Tagged with
Filter by
Sorted by
Tagged with
22 votes
4 answers
5k views

How does SQL recursion actually work?

Coming to SQL from other programming languages, the structure of a recursive query looks rather odd. Walk through it step by step, and it seems to fall apart. Consider the following simple example: ...
UnLogicGuys's user avatar
19 votes
1 answer
36k views

CTE: Get all parents and all children in one statement

I have this working CTE example. I can select all grand-parents and all children. But how can I select all grand-parents and all children in one statement? In this example I want Grandfather, Father, ...
guettli's user avatar
  • 1,521
18 votes
1 answer
13k 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 ...
JohnCand's user avatar
  • 515
17 votes
2 answers
14k 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 ...
markokstate's user avatar
15 votes
2 answers
6k 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 ...
Diggity's user avatar
  • 151
12 votes
1 answer
1k views

How to filter out Scalar Valued User-Defined Function usage from SQL Server Audit Data?

We have a SQL Server database which has a database audit specification which audits all execute actions on the database. CREATE DATABASE AUDIT SPECIFICATION [dbAudit] FOR SERVER AUDIT [servAudit] ADD ...
Mark Iannucci's user avatar
9 votes
2 answers
2k views

Why doesn’t this recursive CTE with a parameter use an index when it does with a literal?

I am using a recursive CTE on a tree structure to list all the descendants of a particular node in the tree. If I write a literal node value in my WHERE clause, SQL Server seems to actually apply the ...
binki's user avatar
  • 454
8 votes
2 answers
409 views

How can I efficiently traverse graph data with this pattern?

I have some relations embodying a directed acyclic graph that includes patterns similar to the following: I'm looking for an efficient way to traverse this graph data. Here is an example of the ...
alx9r's user avatar
  • 421
8 votes
1 answer
6k 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 ...
njvds's user avatar
  • 101
7 votes
1 answer
4k views

Recursive CTE based on function values significantly slower on Postgres 12 than on 11

Following up on my question about some queries in Postgres 12 being slower than in 11 I think, I was able to narrow down the problem. It seems like one recursive CTE based on function values is the ...
cis's user avatar
  • 449
7 votes
1 answer
2k 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 ...
user4150760's user avatar
  • 1,099
7 votes
2 answers
6k 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 ...
GaNeSh GoRdE's user avatar
7 votes
2 answers
4k 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, row(n+1)....
sr33's user avatar
  • 71
6 votes
1 answer
2k views

String manipulation in Recursive CTE

I am trying to make something like the following work: WITH results(n, string ) AS ( SELECT 1,'lol' UNION ALL SELECT n+1, string + ' lol' FROM results ...
Oscar's user avatar
  • 249
6 votes
1 answer
2k views

Help with CTE Aggregating Children Recursively

I'm trying to create a query to calculate the "Available to Sell" qty of all items in inventory. In this case, an item can have current available inventory, but could also be a kit, which is comprised ...
crichavin's user avatar
  • 403
5 votes
2 answers
270 views

CTE query that fails to find ancestors

I have a table foo with the content So the records of the table foo can graphically be represented with this graph. When I run a procedure that should return ancestors using @starting_id=9 (...
Anders Lindén's user avatar
5 votes
1 answer
2k views

How to turn a set of flat trees into a single tree with multiple leaves?

We have this beautiful Postgres tree generator. Yet it kind of produces cuts of a tree not a whole tree all at once: item_id jsonb_pretty 1 { "title": "PARENT", "...
Blender's user avatar
  • 75
5 votes
2 answers
7k views

Create JSON object from recursive tree structure

Having this simple many-to-many self-referential structure. An item owns other items through the joins table: CREATE TABLE items ( item_id serial PRIMARY KEY , title text ); CREATE TABLE ...
Ben's user avatar
  • 179
5 votes
2 answers
257 views

How to group all linked objects in a undirected cyclic graph

I have a table that has customers and its joint customers. e.g Customer 1 has a joint customer 2. Customer 1 is also a joint customer to Customer 3. I am trying to group all customers that are linked ...
Arcturus's user avatar
  • 213
4 votes
1 answer
967 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 ...
crokusek's user avatar
  • 2,120
4 votes
1 answer
2k views

Preserve order of array elements after join

I have a query that returns a CTE looking like +-----------+-------------+ | node_id | ancestors | |-----------+-------------| | 1 | [] | | 2 | [] | | 3 |...
Eric Koslow's user avatar
4 votes
1 answer
5k 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 ...
PAP's user avatar
  • 43
4 votes
1 answer
2k views

How to do hierarchical queries without CTE tables or recusive queries in Snowflake? (parent, child relationships)

I have several tables in Postgres that we are migrating to Snowflake. Some of these table are hierarchical (a foreign key point to the same table), normally to query this data I use CTE tables and/or ...
Arthur Putnam's user avatar
4 votes
1 answer
56 views

best practice for dependency roots

When a database has a dependency table showing the parent-child relationship of items... I've mostly seen guides that assume the top level parent has a "parent id" field which is either zero ...
David's user avatar
  • 41
4 votes
1 answer
141 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] [...
Yuri Ferrari's user avatar
3 votes
1 answer
1k views

Recursive CTE Multiple Levels [duplicate]

I have a table showing which roles recursively grant access to which resources within a database. For example: The Default_Role grants access to the App_Role, the App_Role grants access to the ...
eleven11's user avatar
3 votes
3 answers
398 views

Why can't unused columns and tables be ignored in a recursive CTE

Usually, SQL Server can optimize away any unused columns in the execution plan, and unused joined tables are not queried. But as soon as a recursive CTE comes into play, apparently all columns and ...
djk's user avatar
  • 133
3 votes
2 answers
4k views

Get Nested/Hierarchical Data from SQL Table

I have a table relationship between 'Tag' and 'Task'. It is M:M. The relationship is mapped in the table 'TagTaskMapping'. The TagAssignment table stores the relationship between a Tag and a date. So ...
K09's user avatar
  • 1,394
3 votes
1 answer
422 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 ...
Evan Carroll's user avatar
3 votes
1 answer
725 views

Get the total count of every child foreign keys

I have been trying this in SQL EXPRESS 2016. Here is my problem: I have a tree like structure DB(Parent > child > grandchild >...) and in the lower level there is a relation with another table. ...
CromixPT's user avatar
3 votes
2 answers
5k views

SQL Server CTE Bottom to Top Recursive with Where clause

I have an Employee Table with an EmployeeId, ManagerId and a Name field. The goal is to make a recursive With fetching all the rows from an employee to the top manager (ManagerId is null). I found ...
Lenny32's user avatar
  • 133
3 votes
1 answer
385 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 ...
nunatak's user avatar
  • 31
3 votes
2 answers
583 views

Rolling sum aggregates with self-referencing conditions

I have what I believe is a use-case for a recursive CTE, but I've yet to figure out how to structure it. The requirement is to create a rolling sum total over an amortization schedule, but payments ...
TH58PZ700U's user avatar
3 votes
1 answer
3k views

Help on Self-Referential CTE SQL SERVER

I have this table below which is has a hierarchy to it where the number of levels vary, for this example I chose a subset with 7 levels. L_Key Parent Level P_Key ------------------------- 1393 ...
Kevin Nguyen's user avatar
2 votes
1 answer
645 views

Recursive CTE in PostgreSQL to generate data with different frequencies

I am trying to write a data generation query which uses recursive CTE in Postgresql 14. Consider schema "sc" which includes a function getfreq. getfreq takes an int as param (which ...
Jurgen Cuschieri's user avatar
2 votes
1 answer
657 views

Why my not-null columns become nullable in my CTE recursive query?

I have a simple hierarchical table. Let's say it has these columns: Id, [Guid], ParentId, Title Only ParentId is nullable. Other columns are not nullable. I have created a recursive query to show me ...
Alireza Hosseinitabar's user avatar
2 votes
1 answer
5k 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, ...
Mike's user avatar
  • 167
2 votes
1 answer
419 views

How can I change string output in a hierarchy tree with PostgreSQL?

I have a table like this: ------------------------------------------------- | id | description | parent_id | cost -------------------------------------------------- | 1 | Radiology ...
tucomax's user avatar
  • 37
2 votes
2 answers
3k 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 ...
Mike's user avatar
  • 167
2 votes
2 answers
518 views

Recursively get ids of tree

I have a table foo. So we have a hiearchical object structure where on foo has one parent (or none) and a list of children. What I want is a sql statement that lists all id:s that can be found either ...
Anders Lindén's user avatar
2 votes
1 answer
1k views

Why is my recursive CTE so much slower on Azure SQL?

I have this simple recursive CTE for a hierarchy of folders and their paths: WITH paths AS ( SELECT Id, ParentId, Name AS [Path] FROM Folders WHERE ParentId IS NULL UNION ALL SELECT ...
Eric B's user avatar
  • 213
2 votes
1 answer
2k views

CTE Query to CONCAT text (over 10,000 rows)

I am trying to construct a CTE query to loop through rows of data and concatenate text into one line for each SampleUserNumber. Below is what I am working with: CREATE TABLE #SampleTable ( ...
HelloWorld2099's user avatar
2 votes
1 answer
157 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 | ...
Evan Carroll's user avatar
2 votes
1 answer
750 views

postgresql Recursive Update resulting in Sequential Scan

I have a table with a self-referential foreign key, and I would like to update a given parent, and all of the descendants of the parent, with the same recursive update. When using an UPDATE with a ...
Matthew Moisen's user avatar
2 votes
1 answer
2k views

WITH RECURSIVE with union among the recursive term

Is it possible to use WITH RECURSIVE queries in PostgreSQL where the recursive term is a union of different tables? My usecase is to extract some tuples from a table and some logical rules: And I ...
jackb's user avatar
  • 123
2 votes
1 answer
312 views

Sql query for all ascendent of a node

I have a table having records as below: ID Name parentId 1 A Null 2 B 1 3 C Null 4 D 3 5 E 4 I need out put in following format ID Name ParentName ...
Rohit Kumar's user avatar
2 votes
0 answers
18 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 ...
surfmuggle's user avatar
2 votes
0 answers
138 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. ...
Viz Krishna's user avatar
2 votes
1 answer
2k 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 ...
Itan Reimbergh's user avatar
2 votes
1 answer
115 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 ...
user1664043's user avatar