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

How do you dynamically adjust the recursion depth for eager loading in the SQLAlchemy ORM?

I have a two-table hierarchical setup where table A references table B, which then references back to a different record in table A, and so on... but only up to a given recursion depth. I have this working well using SQLAlchemy and declarative. I'm…
Russ
  • 10,835
  • 12
  • 42
  • 57
8
votes
2 answers

Laravel return all the ids of descendants

How do I return all the ids of AllSubSections (all levels) class Section extends Model { public function Ads() { return $this->hasMany(Ad::class); } public function AllSubSections() { return…
Abanoub
  • 3,623
  • 16
  • 66
  • 104
8
votes
2 answers

Recursively concat columns in sql

I have a table containing values as follows ╔═══╦════╦════╦══════╦══════╗ ║ b ║ l1 ║ l2 ║ l3 ║ l4 ║ ╠═══╬════╬════╬══════╬══════╣ ║ a ║ b1 ║ c1 ║ d1 ║ e1 ║ ║ d ║ x1 ║ y1 ║ null ║ null ║ ╚═══╩════╩════╩══════╩══════╝ The output should…
techno
  • 192
  • 13
8
votes
3 answers

SQL Server Equivalent of Oracle 'CONNECT BY PRIOR', and 'ORDER SIBLINGS BY'

I've got this Oracle code structure I'm trying to convert to SQL Server 2008 (Note: I have used generic names, enclosed column names and table names within square brackets '[]', and done some formatting to make the code more readable): SELECT…
user1058946
  • 243
  • 2
  • 3
  • 14
8
votes
1 answer

Using a WITH inside of a RECURSIVE WITH in PostgreSQL

In PostgreSQL, a WITH can be used by another WITH, for example: WITH num AS ( VALUES (50) ), num2 AS ( SELECT column1 * 2 AS value FROM num ) SELECT value FROM num2; And then there are RECURSIVE WITHs, which can be done like so: WITH…
Trevor Young
  • 545
  • 4
  • 12
7
votes
2 answers

Building a Table Dependency Graph With A Recursive Query

I am trying to build a dependency graph of tables based on the foreign keys between them. This graph needs to start with an arbitrary table name as its root. I could, given a table name look up the tables that reference it using the…
Mark Roddy
  • 27,122
  • 19
  • 67
  • 71
7
votes
2 answers

use recursive common table expressions to find consecutive no.s from two tables

i have the following tables: Actual Optional ------ -------- 4 3 13 6 20 7 26 14 19 21 27 28 What i…
psy
  • 914
  • 3
  • 10
  • 31
7
votes
4 answers

SQL (Maria DB) split string separated by comma to rows

I have this column: names John, Mary Joseph Eleanor, Sophia, Dani And I want this output: names John Mary Joseph Eleanor Sophia Dani And it should include the SUBSTRING_INDEX function
Ana
  • 79
  • 1
  • 3
7
votes
4 answers

Oracle 11 query runs fast on first 2 executions, slower on subsequent, no plan change

Oracle Database 11g Release 11.2.0.4.0 - 64bit Production Solved: was caused by cardinality feedback. I thought I had tested this earlier and eliminated it, but clearly got it wrong. Added this to query: select --+…
Alistair Bayley
  • 331
  • 2
  • 14
7
votes
1 answer

SQL recursive query

I have a Table Category, 1) Id 2) CategoryName 3) CategoryMaster with data as: 1 Computers 0 2 Software 1 3 Multimedia 1 4 Animation 3 5 Health 0 6 Healthsub 5 and i have created recursive query as: ;WITH CategoryTree AS ( SELECT *,…
Milan Solanki
  • 1,207
  • 4
  • 25
  • 48
7
votes
2 answers

Create a recursive view that has a "with recursive" statement in Teradata

I would like to create a recursive view in Teradata (i.e., CREATE RECURSIVE VIEW) from the following reproducible example: CREATE VOLATILE TABLE vt1 ( foo VARCHAR(10) , counter INTEGER , bar INTEGER ) ON COMMIT PRESERVE ROWS; INSERT…
JasonAizkalns
  • 20,243
  • 8
  • 57
  • 116
7
votes
1 answer

Recursion On Database Query to get hierarchical result using Hibernate - Java

I have a table in my Oracle database with child parent relationship like - What I need is to access the list of child in hierarchical manner in Hibernate. When Father logs in - he gets Son as child. When Grandfather logs in - he gets Son, Father,…
Dev Utkarsh
  • 1,377
  • 2
  • 18
  • 43
7
votes
3 answers

How To Get Entire Linked Group Details using SQL?

In have table called "MYGROUP" in database. I display this table data in tree format in GUI as below: Vishal Group | |-------Vishal Group1 | |-------Vishal Group1.1 | |-------Vishal Group1.1.1 | |-------Vishal Group2 | …
Vishal Tiwari
  • 739
  • 2
  • 12
  • 28
7
votes
2 answers

SQL Server 2012 CTE Find Root or Top Parent of Hierarchical Data

I'm having an issue trying to recursively walk a hierarchy to find the top node of all descendent nodes in an organizational structure that may have multiple top-level nodes. I'm trying to use a SQL Server 2012 CTE to do so, but it won't recurse to…
razaross444
  • 513
  • 4
  • 15
7
votes
1 answer

How to concatenate field values with recursive query in postgresql?

I have a table in PostgreSQL database that contains parts of addresses in a form of a tree and looks like this: Id | Name | ParentId 1 | London | 0 2 | Hallam Street| 1 3 | Bld 26 | 2 4 | Office 5 | 3 I would like to…
mofoyoda
  • 695
  • 2
  • 10
  • 16
1 2 3
…
99 100