Stack Overflow
Stack Overflow

Questions tagged [recursive-query]

The ability to run recursive queries in SQL

Recursive SQL queries offer the ability to retrieve hierarchical information in a relational database (e.g. "adjacency list model"). The SQL standard defines recursive common table expressions (CTE) for this purpose. Some vendors such as Oracle have implemented this functionality with a proprietary syntax (CONNECT BY). Recursive CTEs are supported by a wide range of DBMS such as Oracle, SQL Server, DB2, PostgreSQL, Firebird SQL, Teradata and others.

2024 questions
20
votes
2 answers

PostgreSQL WITH RECURSIVE performance

I have a simple question. Somehow I was unable to find a definitive answer. How much is WITH RECURSIVE syntax optimized in PostgreSQL? By that I mean: is it merely a syntactic sugar for a series of non recursive queries, OR is it more of a single…
julx
  • 8,694
  • 6
  • 47
  • 86
20
votes
1 answer

How to traverse a hierarchical tree-structure structure backwards using recursive queries

I'm using PostgreSQL 9.1 to query hierarchical tree-structured data, consisting of edges (or elements) with connections to nodes. The data are actually for stream networks, but I've abstracted the problem to simple data types. Consider the example…
Mike T
  • 41,085
  • 18
  • 152
  • 203
18
votes
5 answers

Recursive select in SQL

I have an issue I just can't get my head around. I know what I want, just simply can't get it out on the screen. What I have is a table looking like this: Id, PK UniqueIdentifier, NotNull Name, nvarchar(255), NotNull ParentId, UniqueIdentifier,…
MrW
  • 1,210
  • 2
  • 16
  • 27
18
votes
3 answers

How to grep a word inside xml files in a folder

I know I can use grep to find a word in all the files present in a folder like this grep -rn core . But my current directory has many sub-directories and I just want to search in all xml files present in the current directory and its all sub…
Sachin Jain
  • 21,353
  • 33
  • 103
  • 168
17
votes
8 answers

MySQL Recursive get all child from parent

i have this case using recursive query on Mysql to find lv 2 and lv3 child on one table... database structure i'm using: id name parent 1 A 0 2 B 0 3 C 0 4 D 1 5 E 1 6 F 2 7 G 2 8 H 3 9 I 3 10 …
Bakti Wijaya
  • 447
  • 1
  • 6
  • 21
17
votes
4 answers

Recursive JPA query?

Does JPA 2 have any mechanism for running recursive queries? Here's my situation: I have an entity E, which contains an integer field x. It also may have children of type E, mapped via @OneToMany. What I'd like to do is find an E by primary key, and…
Mike Baranczak
  • 8,291
  • 8
  • 47
  • 71
16
votes
1 answer

Prevent infinite loop in recursive query in Postgresql

Here: (To find infinite recursive loop in CTE) is a discussion how to prevent an infinite loop in a recursive query. There the recursion is prevented on the "query level" - at least in an answer about Postgresql. Is there a way in Postgresql (10) to…
madflow
  • 7,718
  • 3
  • 39
  • 54
15
votes
8 answers

get parents and children of tree folder structure in my sql < 8 and no CTEs

I have a folder table that joins to itself on an id, parent_id relationship: CREATE TABLE folders ( id int(10) unsigned NOT NULL AUTO_INCREMENT, title nvarchar(255) NOT NULL, parent_id int(10) unsigned DEFAULT NULL, PRIMARY KEY…
dagda1
  • 26,856
  • 59
  • 237
  • 450
15
votes
1 answer

SQL Server recursive query

I am new to SQL Server development. Most of my experience has been done with Oracle. suppose I have the following table that contains Appointments objects CREATE TABLE [dbo].[Appointments]( [AppointmentID] [int] IDENTITY(1,1) NOT NULL, …
Lorenzo
  • 29,081
  • 49
  • 125
  • 222
15
votes
2 answers

Recursive query used for transitive closure

I've created a simple example to illustrate transitive closure using recursive queries in PostgreSQL. However, something is off with my recursive query. I'm not familiar with the syntax yet so this request may be entirely noobish of me, and for that…
Dowwie
  • 1,893
  • 3
  • 18
  • 23
14
votes
2 answers

recursive path aggregation and CTE query for top-down tree postgres

I'm trying to write a query to produce a list of all nodes in a tree given a root, and also the paths (using names the parents give their children) taken to get there. The recursive CTE I have working is a textbook CTE straight from the docs here,…
pyramation
  • 1,631
  • 4
  • 22
  • 35
14
votes
1 answer

Infinite loop in CTE when parsing self-referencing table

I'm using the following Common Table Expression to parse self-referencing table. But the CTE does not work, produces and infinite loop and generates an error: Msg 530, Level 16, State 1, Line 1 The statement terminated. The maximum recursion 100…
jrara
  • 16,239
  • 33
  • 89
  • 120
13
votes
1 answer

sql select parent child recursive in one field

I do not know how to select query recursive.. id idparent jobNO -------------------------------- 1 0 1 2 1 2 3 1 3 4 0 4 5 4 5 6 4 …
h.madanizadegan
  • 133
  • 1
  • 1
  • 5
13
votes
4 answers

Oracle SQL Analytic query - recursive spreadsheet-like running total

I have the following data, composed of the A value, ordered by MM (month). The B column is computed as GREATEST(current value of A + previous value of B, 0) in a spreadsheet-like fashion. How can I compute B using a SQL Query? I tried using…
Danilo Piazzalunga
  • 7,590
  • 5
  • 49
  • 75
12
votes
3 answers

PostgreSQL recursive parent/child query

I'm having some trouble working out the PostgreSQL documentation for recursive queries, and wonder if anyone might be able to offer a suggestion for the following. Here's the data: Table "public.subjects" …
knirirr
  • 1,860
  • 4
  • 23
  • 37
1
2
3
…
99 100