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
12
votes
1 answer

How does one print depth-level in a Postgres query that uses RECURSIVE to select descendants?

I have a table persons that contains a column for parent_id, which refers to another row in the same table. Assume this is the logical hierarchy: P1 P2 P3 P4 P5 P6 P7 P8 P9 P10 I have written a query that prints all…
thatharmansingh
  • 163
  • 1
  • 1
  • 7
11
votes
3 answers

SQL Multi Condition CTE Recursion

I the database i have the 2 following pieces of information for each identifier. The company that controls them, and companies where they have small bits of control. Something along the lines, 2 tables(ignoring some unique…
blueomega
  • 281
  • 2
  • 15
11
votes
4 answers

Simple recursive query in Oracle

I'm currently having some trouble understanding and writing recursive queries. I understand that recursive queries are used to search through hierarchies of information, but I haven't found a simple solution online that can travel up a hierarchy.…
ac_nook
  • 325
  • 1
  • 2
  • 11
11
votes
3 answers

Recursive query challenge - simple parent/child example

Note: with help from RhodiumToad on #postgresql, I've arrived at a solution, which I posted as answer. If anyone can improve on this, please chime in! I have not been able to adapt a previous recursive query solution to the following directed…
Dowwie
  • 1,893
  • 3
  • 18
  • 23
10
votes
2 answers

How CTE really works?

I came across this CTE solution for concatenating row elements and I thought it's brilliant and I realized how powerful CTEs can be. However, in order to use such a tool effectively I need to know how it works internally to build that mental image…
Elemeen Elsayed
  • 163
  • 4
  • 14
10
votes
3 answers

Recursive stored functions in MySQL

I'm trying to make a function that recursively builds a path for a specific category CREATE FUNCTION getPath(inId INT) RETURNS TEXT DETERMINISTIC BEGIN DECLARE return_path TEXT; DECLARE return_parent_id INT; SELECT CONCAT('/', name) INTO…
tirithen
  • 3,219
  • 11
  • 41
  • 65
10
votes
5 answers

Prevent and/or detect cycles in postgres

Assuming a schema like the following: CREATE TABLE node ( id SERIAL PRIMARY KEY, name VARCHAR, parentid INT REFERENCES node(id) ); Further, let's assume the following data is present: INSERT INTO node (name,parentid)…
sschober
  • 2,003
  • 3
  • 24
  • 38
10
votes
2 answers

Recursive SQL statement (PostgreSQL 9.1.4)

PostgreSQL 9.1 Business situation Every month, there is a new batch of accounts given to a specific process. Every batch can be described by month, number of accounts and total balance of accounts. The goal of the process is to recover some of the…
Tomas Greif
  • 21,685
  • 23
  • 106
  • 155
9
votes
2 answers

finding shortest path up to ten degrees of separation

I have the following three tables in SQL: select * from movie limit 2; id | title | year | content_rating | duration | lang | country | gross | budget | director_id…
Programmer
  • 1,266
  • 5
  • 23
  • 44
9
votes
1 answer

Deleting rows recursively in a self-referencing table using a CTE. How does the process take place?

I'm working on a side project, and in order to delete a row and all its descendants in a self-referencing table, I'm using a recursive CTE like this inside a trigger: CREATE TRIGGER dbo.tr_Comment_Delete ON dbo.Comment INSTEAD OF DELETE AS …
eddy
  • 4,373
  • 16
  • 60
  • 94
9
votes
1 answer

Symfony2 recursive query builder

I am having a data-table which contains media,docs,pdfs and folders list. Same as Google Drive, I want to show breadcrumb at the top of the data-table. The data-table reflects the changes when clicks on any folders through jQuery. All things are…
Yash Parekh
  • 1,513
  • 2
  • 20
  • 31
9
votes
3 answers

Create nested json object using php mysql

I have two tables, table 1 has 2 fields (question_pk, question_name) and table 2 has 4 fields(ans_pk, options, question_fk and right_answer). I want to create json like the following structure { "type": "quiz", "name": "Brand Colors", …
Vetrivel
  • 1,149
  • 1
  • 7
  • 16
9
votes
3 answers

How to show recursive parentID in a single column in SQL

Here is the example structure of the table: ID Name ParentID ----------------------- 1 Ancestor NULL 2 GrandFather 1 3 GrandMother 1 4 Child 3 I'm trying to write a query that would return ID Name …
darthun08
  • 105
  • 1
  • 2
  • 8
8
votes
3 answers

CONNECT BY or hierarchical queries in RDBMS other than Oracle

Oracle ships with a very handy feature. You can create hierarchical queries (recursive behaviour) using the following clause: CONNECT BY [NOCYCLE] {condition [AND condition...]} [START WITH condition] As documented…
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
8
votes
3 answers

sqlite recursive ancestor query

I'm trying to figure out how to use a recursive query with a hierarchical table. I need to get the ancestors of a given record, and the records should be sorted in order of their level in the hierarchy. That is, the first record should be the top…
tscheingeld
  • 789
  • 7
  • 24
1 2
3
…
99 100