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

Producing a path from each node to the root of a tree represented as edges in a table

I have the following table on a PostgreSQL database (parent_fk is a foreign key that references the same table): id | parent_fk 72 | 342 | 72 583 | 342 I want to query this table and discover the path of each element to the…
Fernando Magalhães
  • 539
  • 2
  • 9
  • 17
5
votes
2 answers

recursive query with peer relations

Let's say there is a table of relationships (entity_id, relationship, related_id) 1, A, 2 1, A, 3 3, B, 5 1, C, null 12, C, 1 100, C, null I need a query that will pull all related rows. For example, if i queried for entity_id = 1,…
mson
  • 7,762
  • 6
  • 40
  • 70
4
votes
1 answer

MySQL, recursive CTE. How does it work in such a syntactic form?

Common recursive CTE looks like: WITH RECURSIVE cte AS ( SELECT 1 id UNION ALL SELECT id + 1 FROM cte WHERE id < 1000 ) SELECT COUNT(*) FROM cte; This form is well-described in Reference Manual. But the same output can be produced while using…
Akina
  • 39,301
  • 5
  • 14
  • 25
4
votes
2 answers

Gaps and island combined with a recursion

I have a gaps and islands SQL problem combined with a recursion. Background: I run a PostgreSQL database with school vacations and bank holidays from which I render calendar views for schools, city and what not. I code a lot of the logic with the…
wintermeyer
  • 8,178
  • 8
  • 39
  • 85
4
votes
2 answers

How to carrying over values for missing dates in time series using last value windows analytical functions in mysql

How to carrying over values for missing dates postcode/indicator_category to create full monthly time series. Im trying to use last_value to carry over values but not able to make it. Is my approach correct? Any help would by highly…
marjun
  • 696
  • 5
  • 17
  • 30
4
votes
1 answer

Recursively count occurrences with sqlite

Consider the following example table x_id name_id1 name_id2 x1 John Frank x2 Frank John x3 Jack John x4 John Jack x5 Bob Frank x6 George Bob x7 Bob Finn x8 Mark James x9 James Finn The goal is to extract the following…
N Meibergen
  • 362
  • 2
  • 14
4
votes
1 answer

Recursive INSERT query for a grouped sequence

I have two tables like this: Table1ID Table2ID Table1ID SomeDate -------- ------------------------------ 1 1 1 2011-01-01 2 2 1 …
AJ.
  • 16,368
  • 20
  • 95
  • 150
4
votes
1 answer

PostgreSQL merge recursive query and JOIN

I have the following schema: CREATE TABLE tbl_employee_team ( employee_id int, teams_id int ); INSERT INTO tbl_employee_team VALUES (1, 2), (1, 3), (1, 4); CREATE TABLE tbl_team_list_serv ( service_id int, team_id int ); INSERT INTO…
Joe D
  • 44
  • 1
  • 10
4
votes
2 answers

MySql Recursive - get all children and parents from a given id

MySQL Version 8.0 Schema SQL CREATE TABLE IF NOT EXISTS `department` ( `id` INT NOT NULL, `name` VARCHAR(45) NOT NULL, `father` INT NULL, PRIMARY KEY (`id`), INDEX `fk_department_department_idx` (`father` ASC) VISIBLE, CONSTRAINT…
Fábio
  • 69
  • 2
  • 6
4
votes
1 answer

How do I make my CTE search through my data again in T-SQL/MSSQL?

Currently, I have a list of time entries for employees that looks like this: TimeSheetId TechnicianUserId TimeSheetDate FromDatetime ToDatetime 1215286 4730 2020-11-10 2020-11-10 14:15:00.000 …
Don Ford
  • 105
  • 10
4
votes
3 answers

Full recursive employee-boss relation in SQL Server

I need to get the name of all of the employees that depends of a person directly or indirectly. Using the query in this example (from https://rextester.com/WGVRGJ67798), create table employee( id int not null, employee varchar(10) not null, boss int…
d2907
  • 798
  • 3
  • 15
  • 45
4
votes
3 answers

Managing multiple categories trees, using Python and PostgreSQL

I have multiple categories, which can have None or one or multiple sub-categories. The process theoretically can go to infinite. So, it is like having multiple trees. Tree example. A - A1 - A11 - A12 -A2 B C - C1 I have also Item(s). An…
user3541631
  • 3,686
  • 8
  • 48
  • 115
4
votes
2 answers

How to use a recursive query as a subquery?

I need to write a query that calls a recursive query many times. I was not able to figure out how to do. I guess I can do this by using a cursor, preparing the sql statement at run time and then use EXEC(mySQLstatement) to run it at every cursor…
UnDiUdin
  • 14,924
  • 39
  • 151
  • 249
4
votes
1 answer

How can I turn the output of this stored procedure that uses a temporary table into a materialized view?

I have a stored procedure that produces the correct output, and I'd like to store the results in a materialized view. I'm having trouble conceptualizing how to get this working because the procedure relies on a temporary table that I created called…
Nathan Jones
  • 4,904
  • 9
  • 44
  • 70
4
votes
1 answer

PostgreSQL WITH RECURSIVE query to get ordered parent-child chain by a Partition Key

I have the issue writing a sql script on PostgreSQL 9.6.6 which orders steps in a process by using the steps' parent-child ID's, and this grouped/partitioned per process ID. I couldn't find this special case here, so I apologize if I missed it and…
Marc
  • 43
  • 5
1 2 3
…
99 100