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
0
votes
0 answers

How do I join a table with itself to get the data's hierarchy

A section of my database includes a table saving information about blocks: CREATE TABLE blocks ( id_block INTEGER NOT NULL, id_type INTEGER NOT NULL, id_parent_block INTEGER NOT NULL, PRIMARY KEY (id_block), FOREIGN KEY (id_type)…
Atua Mae
  • 1
0
votes
1 answer

How to get all the children for all the parents in hierarchical data

I have a table that has child_id and parent_id. The child_id has the leaf node and the parent_id has the immediate parent. I need to get all the parents and all their children using a SQL query Could someone help me with this? I tried a recursive…
Shravan Vijayaprasad
  • 23
  • 1
  • 6
0
votes
2 answers

How to create a query with all of dependencies in hierarchical organization?

I've been trying hard to create a query to see all dependencies in a hierarchical organization. But the only I have accuaried is to retrieve the parent dependency. I have attached an image to show what I need. Thanks for any clue you can give…
Javier del Hoyo
  • 57
  • 1
  • 7
0
votes
1 answer

How to get all ancestors of a Django object when ForeignKey is not self-referencing?

I have a model Person and another model Relation. Before creating a new relation I want to check if this relation is possible or not. This post and some other similar posts provides a solution but for self-referencing models, my model is not self…
ak4zh
  • 3
  • 2
0
votes
2 answers

How can I link these rows together in an SQL query in BigQuery? (I think this requires CTE recursion, which BigQuery doesn't seem to like...)

any help greatly appreciated. Quite stumped on this one! Amateur at SQL, but think BigQuery isn't helping me out here. I have a table that looks something like this: Object ID1 ID2 A 1 null B 2 1 C 3 2 D 4 3 A, B, C and D are…
nakedlunch
  • 11
  • 4
0
votes
1 answer

How to get the root in a hierarchy query using SQL Server from any level of Hierarchy

I would like to get the Top most Ancestor (Root) of the hierarchy from any level of data. The following is my table. CREATE TABLE #SMGROUP (ID INT NOT NULL, GRP NVARCHAR(40), GRPCLASS INT, PARENTGRP NVARCHAR(40), PARENTGRPCLASS INT) INSERT INTO…
Sabyasachi Mukherjee
  • 159
  • 1
  • 1
  • 10
0
votes
0 answers

The stored procedure return Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

;WITH #SearchHistory (OrderDetail_ID, RelatedOrderDetail_ID, OrigOrderDetail_ID, TimesRolled) AS ( SELECT od.OrderDetail_ID ,od.RelatedOrderDetail_ID ,0 ,0 FROM #SearchFilter f1 INNER JOIN…
maruthu
  • 1
0
votes
1 answer

Get all linked IDs of the same table for a given IDs (CTE)

There is a table with the following structure (simplified): RowID FK_RowID 1 3 2 1 3 11 4 2 5 4 6 1 7 8 8 9 9 10 11 What I want to have is that i get a list of all linked RowIDs and there linked RowIDs as well. For…
John
  • 121
  • 10
0
votes
0 answers

Filter tree structure till total item reach its limit

I'm trying to filter an array like tree structure recursively to it will be limited to maximum number of items to be display. export interface MyType { children: MyType[], id: number; name:string; } items: MyType[] = [{ children: [{ …
Hung Bui
  • 313
  • 4
  • 17
0
votes
1 answer

Recursive Iterator for a single table

I have a table in which the foreign key refers to the id of the same table. I need to find the children of the first element that pass me as a parameter, until I reach a certain level. I need to find all the children of one element. The first time I…
KikeSP
  • 103
  • 1
  • 9
0
votes
2 answers

Filter one record in table with Request ID

I have tabel request_process as follows: requestID Status 1 0 1 1 1 2 2 0 2 1 I want to get one row per request with maximum status but only those rows whose status for request is not 2 I want…
Muhammad Muazzam
  • 2,810
  • 6
  • 33
  • 62
0
votes
1 answer

Select a group of multiple children per each member of a selected group of parents (with their own parents as well)

I have the following scheme built using PostgreSQL - Table A: id | name 1 | a 2 | b Table B: id | parent-A-id | searchValue | dataB 1 | 1 | val1 | a 2 | 2 | val2 | b 3 | 2 …
Yehonatan
  • 3,168
  • 7
  • 31
  • 39
0
votes
1 answer

How to get a hierarchical category products from top to down in sql

I keep product categories with parentid logic. I need to get all products under a category, but from top to down hierarchy. For example I have these tables: ProductCategories id CategoryID ProductID 1 4 1 2 5 2 3 6…
Zeynep
  • 159
  • 1
  • 15
0
votes
1 answer

Fetch nodes recursively with Gremlin query

I'd like to define a Gremlin query which returns all the nodes recursively till there are no more nodes available: Node: ProductionEvent Node: Product What I've tried so far is the following...however, I'm not sure if the output is correct. Also,…
haminoum
  • 71
  • 6
0
votes
0 answers

Recursive CTE in SQL to replace phantom builds in bill of materials

I have built a recursive CTE to blow out my bill of materials but many items have "phantom" parts in the bill of materials. I need to replace the "phantom" items with their bill of materials. Phantom items arent actual items but are a way we group a…
Daniel
  • 37
  • 4
1 2 3
…
99
100