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
6
votes
2 answers

SQL Server Hierarchical Sum of column

I have my database design as per the diagram. Category table is self referencing parent child relationship Budget will have all the categories and amount define for each category Expense table will have entries for categories for which the amount…
Sachin Trivedi
  • 2,033
  • 4
  • 28
  • 57
6
votes
2 answers

Make a copy of parent-child structure in SQL

I have a table MODELS to which several ITEMS can belong. The ITEMS table is a hierarchical table with a self join on the PARENT column. Root level items will have Null in PARENT. Items can go to any level deep. create table MODELS ( MODELID …
navigator
  • 1,678
  • 16
  • 29
6
votes
1 answer

MySQL Recursive query to find the shortest path

I have an issue I just can't get my head around... I have a table called country_neighbour looking like…
Davhoj
  • 71
  • 3
6
votes
1 answer

How sql with-recursive statement interpreted?

I would like to ask get some help about understanding how "with recursive" works. More precisely WHY the anchor query (the non-recursive term) isn't replicated into the sub call of the CTE. I tried my best to understand alone but I'm not sure. First…
Falt4rm
  • 915
  • 6
  • 21
6
votes
3 answers

WITH RECURSIVE query to choose the longest paths

I am new to WITH RECURSIVE in PostgreSQL. I have a reasonably standard recursive query that is following an adjacency list. If I have, for example: 1 -> 2 2 -> 3 3 -> 4 3 -> 5 5 -> 6 it produces: 1 1,2 1,2,3 1,2,3,4 1,2,3,5 1,2,3,5,6 What I would…
brechmos
  • 1,278
  • 1
  • 11
  • 22
6
votes
2 answers

Recursive calculation to form a tree using sql

I am working on a simple problem and wanted to solve it using SQL. I am having 3 tables Category, Item & a relational table CategoryItem. I need to return count of items per category but the twist is Categories are arranged in Parent-Child…
user2443648
  • 141
  • 8
6
votes
2 answers

Get all trees from set of rows data (with specific id) in tsql

My data is in table with 2 field, Id & ParentId. I store data with this structure(similar image in follow). How can I get all path from leaf to root that include Id = 6 ? (Result sample is in follow) --Data structure is as follow : -- 1 -- / --2 <-…
Ali Ahmadi
  • 2,387
  • 4
  • 31
  • 48
6
votes
2 answers

Nested cos() calculation in Oracle 10

I have table with some positive integer numbers n ---- 1 2 5 10 For each row of this table I want values cos(cos(...cos(0)..)) (cos is applied n times) to be calculated by means of SQL statement (PL/SQL stored procedures and functions are not…
Egor Skriptunoff
  • 23,359
  • 2
  • 34
  • 64
6
votes
3 answers

Query furthest children in Adjacency List

So I have an SQL query to retrieve all the children of a given node in an adjacency list. WITH RECURSIVE q AS ( SELECT id, name FROM categories h WHERE id = 11846801 UNION ALL SELECT …
OverlordQ
  • 172
  • 1
  • 1
  • 11
6
votes
1 answer

PostgreSQL Recursive via 2 parent/child tables

I would like to create a linear ancestry listing for a tree breeding project. The parents are male/female pairs that must not be related (no inbreeding), hence the importance to track and visualize these pedigrees... Below is the test tables/data…
user1888167
  • 131
  • 3
  • 9
6
votes
2 answers

Find all the supervisors of an employee

I know the answer would seem to be to use "WITH RECURSIVE" as per this post but I'm just not getting it. I have a table called people, and a table called position_hierarchy. The people table has a unique id uperson_id and position id we call pcn…
Paul Tomblin
  • 179,021
  • 58
  • 319
  • 408
5
votes
1 answer

force Oracle to process recursive CTE on remote db site (perhaps using DRIVING_SITE hint)

I am trying to fetch data from remote table. The data is expanded from seed set of data in local table using recursive CTE. The query is very slow (300 seed rows to 800 final rows takes 7 minutes). For other "tiny local, huge remote"-cases with no…
Tomáš Záluský
  • 10,735
  • 2
  • 36
  • 64
5
votes
2 answers

MySQL - Recursively list all parents and ancestors of all items in table

I have a table with a parent/child hierarchy that supports multiple (theoretically infinite) levels of nesting: |------|-------------------|-------------| | id | title | parent_id | |------|-------------------|-------------| | 1 |…
Stephen B
  • 141
  • 2
  • 13
5
votes
1 answer

Is it possible to force SQL Server to use the plan I want to optimise recursive cte query

I have a query in a view using a recursive cte on a large tree that works well when queried with a hardcoded number but not with a parameter. Is it possible to force SQL Server to use the plan I want to optimise this recursive cte query? Any ideas…
Adam Butler
  • 3,023
  • 5
  • 35
  • 40
5
votes
5 answers

how to create a query in sql to chop sentences into words and add them to new table with their frequency

I'm trying to do a query that I'm not sure if it's possible I have a table called sentencess which contain ID, Sentences, and verify as shown in the picture bellow. I have another table called, word count which contains ID, words, and there…
programming freak
  • 859
  • 5
  • 14
  • 34
1 2 3
…
99 100