All Questions
98
questions
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:
...
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, ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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)....
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
...
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 ...
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
(...
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",
"...
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 ...
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 ...
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
...
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 |...
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 ...
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 ...
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 ...
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] [...
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 ...
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 ...
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 ...
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 ...
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.
...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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,
...
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 ...
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 ...
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 ...
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 ...
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
(
...
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 | ...
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 ...
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 ...
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
...
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 ...
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. ...
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
...
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 ...