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

SQL Database Design, recursive parent-child relationship?

I want to represent a recursive parent-child relationship in a MySQL database. I want to create a category-subcategory relationship. A category can have N subcategories and each one of those can have N subcategories and so on. I was thinking of…
Christos Baziotis
  • 5,845
  • 16
  • 59
  • 80
7
votes
5 answers

Best method to search hierarchical data

I'm looking at building a facility which allows querying for data with hierarchical filtering. I have a few ideas how I'm going to go about it but was wondering if there are any recommendations or suggestions that might be more efficient. As an…
William
  • 8,007
  • 5
  • 39
  • 43
7
votes
1 answer

Recursive function in postgres

How to map below query to postgres function. WITH RECURSIVE source (counter, product) AS ( SELECT 1, 1 UNION ALL SELECT counter + 1, product * (counter + 1) FROM source WHERE counter < 10 ) SELECT counter, product FROM source; I am new to postgres.…
PL-SQL Developer1000
  • 133
  • 1
  • 1
  • 4
7
votes
5 answers

Calculating the Weighted Average Cost of products stock

I have to calculate my products stock cost, so for every product after each buy, i have to recalculate the Weighted Average Cost. I got a view thats bring me the current product's stock after each in/out: document_type document_date product_id …
Houari
  • 5,326
  • 3
  • 31
  • 54
7
votes
3 answers

Recursive XML in scala

I am trying to parse this document in scala: …
Felix
  • 8,385
  • 10
  • 40
  • 59
7
votes
4 answers

Oracle Connect By Prior for Recursive Query Syntax

Suppose I had the following table in my oracle DB: ID: Name: Parent_ID: 123 a 234 345 b 123 234 c 234 456 d 345 567 e 567 678 f 567 And what I would like to do is find, for…
John Bustos
  • 19,036
  • 17
  • 89
  • 151
6
votes
1 answer

How can you detect a parent with a nested relationship in a database using SQL?

I'm using Firebird 2.1. There is a table name Folders, with the fields: FolderID ParentFolderID FolderName ParentFolderID is -1 if it's the root folder -- otherwise it contains the parent folder's ID. How can I find all parents (up to the root…
Steve
  • 2,510
  • 4
  • 34
  • 53
6
votes
2 answers

Implement Recursive CTE for Hierarchical Query to MariaDB

I have this table which I would like to store a chain of records. CREATE TABLE table_name ( id INT, unique_id varchar, reference_id varchar, ); I want to implement SQL query for MariDB which prints all records by unique_id with all…
Peter Penzov
  • 1,126
  • 134
  • 430
  • 808
6
votes
0 answers

SQLAlchemy simple recursive cte query

I have the following SQLAlchemy table: from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class NetworkLink(Base): """Network immediate link between a franchisee and his franchisor """ __tablename__ =…
Daniele
  • 153
  • 3
  • 11
6
votes
2 answers

Split values in parts with sqlite

I'm struggling to convert a | a1,a2,a3 b | b1,b3 c | c2,c1 to: a | a1 a | a2 a | a3 b | b1 b | b2 c | c2 c | c1 Here are data in sql format: CREATE TABLE data( "one" TEXT, "many" TEXT ); INSERT INTO "data" VALUES('a','a1,a2,a3'); INSERT INTO…
Adobe
  • 12,967
  • 10
  • 85
  • 126
6
votes
1 answer

Directed graph in Oracle SQL using recursive query visiting each node only once

Description In our problem domain we are working on a set of edges that connect together forming a graph. Starting from a given node (or nodes), we have to list all links within the entire graph that are connected to the given node (or nodes). We…
Bart Gerard
  • 75
  • 6
6
votes
2 answers

SQL Server CTE hierarchy issue

i have an application serve multilevel of permissions and roles i have this HIERARCHY : Country ....Region ........City ............Association ................Center ....................School ........................Class this HIERARCHY i name…
Loai
  • 732
  • 16
  • 32
6
votes
1 answer

How can I make this query recursive Sql Server?

I have this table structure for Balances table: And this is the view: I have also this structure for Amounts table: This is the view mode for Amounts table: First of all I need to get the amount value for a specific day in Amounts Table: with…
Esraa_92
  • 1,558
  • 2
  • 21
  • 48
6
votes
3 answers

How to guarantee that at least N rows are returned by recursive CTE in Postgres

Most resources that describe a SELECT TOP ... query in Postgres say that you should use LIMIT instead, possibly with an ORDER BY clause if you need to select the top elements by some ordering. What do you do if you need to select the top N elements…
ely
  • 74,674
  • 34
  • 147
  • 228
6
votes
4 answers

Implementing a recursive query in SQL

I've a question about the use of recursive SQL in which I have following table structure Products can be in multiple groups (for the sake of clarity, I am not using int ) CREATE TABLE ProductGroups(ProductName nvarchar(50), GroupName…
Tejas Patel
  • 1,289
  • 13
  • 25
1 2 3
…
99 100