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
433
votes
16 answers

How to create a MySQL hierarchical recursive query?

I have a MySQL table which is as follows: id name parent_id 19 category1 0 20 category2 19 21 category3 20 22 category4 21 ... ... ... Now, I want to have a single MySQL query to which I simply supply the id [for instance say…
Tarun Parswani
  • 4,565
  • 3
  • 13
  • 13
81
votes
7 answers

MySQL how to fill missing dates in range?

I have a table with 2 columns, date and score. It has at most 30 entries, for each of the last 30 days one. date score ----------------- 1.8.2010 19 2.8.2010 21 4.8.2010 14 7.8.2010 10 10.8.2010 14 My problem is that some dates are…
Jerry2
  • 2,955
  • 5
  • 30
  • 39
81
votes
6 answers

How to do the Recursive SELECT query in MySQL?

I got a following table: col1 | col2 | col3 -----+------+------- 1 | a | 5 5 | d | 3 3 | k | 7 6 | o | 2 2 | 0 | 8 If a user searches for "1", the program will look at the col1 that has "1" then it will get a value in…
Tum
  • 3,614
  • 5
  • 38
  • 63
63
votes
11 answers

Is it possible to make a recursive SQL query?

I have a table similar to this: CREATE TABLE example ( id integer primary key, name char(200), parentid integer, value integer); I can use the parentid field to arrange data into a tree structure. Now here's the bit I can't work out. Given…
Adam Pierce
  • 33,531
  • 22
  • 69
  • 89
48
votes
5 answers

How does a Recursive CTE run, line by line?

I think I've got the format of Recursive CTEs down well enough to write one, but still find myself frustrated to no end that I cannot manually process one (pretend to be the SQL engine myself and reach the result set with pen and paper). I've found…
justin w
  • 603
  • 1
  • 6
  • 6
43
votes
4 answers

Oracle SYS_CONNECT_BY_PATH equivalent query into SQL Server

I'm trying to convert a complex query involving the Oracle SYS_CONNECT_BY_PATH syntax into SQL Server: SELECT DISTINCT TO_CHAR(CONCAT(@ROOT, SYS_CONNECT_BY_PATH(CONCAT('C_',X), '.'))) AS X_ALIAS ,…
Platus
  • 1,753
  • 8
  • 25
  • 51
37
votes
2 answers

Find Parent Recursively using Query

I am using postgresql. I have the table as like below parent_id child_id ---------------------- 101 102 103 104 104 105 105 106 I want to write a sql query which will give the final parent of input. i.e suppose i pass…
Avadhesh
  • 4,519
  • 5
  • 33
  • 44
35
votes
9 answers

Django self-recursive foreignkey filter query for all childs

I have this model with a self referencing Foreign Key relation: class Person(TimeStampedModel): name = models.CharField(max_length=32) parent = models.ForeignKey('self', null=True, blank=True, related_name='children') Now I want to get all…
Ahsan
  • 11,516
  • 12
  • 52
  • 79
29
votes
5 answers

Error - "UNION operator must have an equal number of expressions" when using CTE for recursive selection

At this moment I have a table tblLocation with columns ID, Location, PartOfID. The table is recursively connected to itself: PartOfID -> ID My goal is to have a select output as followed: > France > Paris > AnyCity > Explanation: AnyCity is located…
user2871811
  • 448
  • 1
  • 4
  • 11
28
votes
4 answers

What is the equivalent PostgreSQL syntax to Oracle's CONNECT BY ... START WITH?

In Oracle, if I have a table defined as … CREATE TABLE taxonomy ( key NUMBER(11) NOT NULL CONSTRAINT taxPkey PRIMARY KEY, value VARCHAR2(255), taxHier NUMBER(11) ); ALTER TABLE taxonomy ADD CONSTRAINT taxTaxFkey FOREIGN…
dacracot
  • 22,002
  • 26
  • 104
  • 152
26
votes
1 answer

How to select using WITH RECURSIVE clause

I have googled and read throug some articles like this postgreSQL manual page or this blog page and tried making queries myself with a moderate success (part of them hangs, while others works good and fast), but so far I can not completely…
user2754703
  • 279
  • 1
  • 3
  • 6
25
votes
2 answers

How to get all children of a parent and then their children using recursion in query

I have structure like this: ... ... …
user1893999
  • 255
  • 1
  • 3
  • 6
22
votes
3 answers

How to list tables affected by cascading delete

I'm trying to perform a cascading delete on 15+ tables but I'm not certain that all of the requisite foreign keys have been configured properly. I would like to check for missing constraints without manually reviewing each constraint. Is there a…
Peter Hanneman
  • 523
  • 1
  • 5
  • 18
22
votes
1 answer

Hierarchical SQL data (Recursive CTE vs HierarchyID vs closure table)

I have a set of hierarchical data being used in a SQL Server database. The data is stored with a guid as the primary key, and a parentGuid as a foreign key pointing to the objects immediate parent. I access the data most often through Entity…
jp36
  • 1,873
  • 18
  • 28
21
votes
1 answer

Tree Structure and Recursion

Using a PostgreSQL 8.4.14 database, I have a table representing a tree structure like the following example: CREATE TABLE unit ( id bigint NOT NULL PRIMARY KEY, name varchar(64) NOT NULL, parent_id bigint, FOREIGN KEY (parent_id)…
jabu.10245
  • 1,884
  • 1
  • 11
  • 20
1
2 3
…
99 100