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

Generating Sequential Months in SQL

I have a table like below sample: code | date | value 1000 2016-08-05 5000 1000 2016-12-27 8000 1000 2018-03-19 6000 1000 2018-06-02 6000 Now I need to generate sequential months like this: code | date |…
programmer21
  • 173
  • 11
0
votes
2 answers

SQL Server 2008: Recursive query where hierarchy isn't strict

I'm dealing with a large multi-national corp. I have a table (oldtir) that shows ownership of subsidiaries. The fields for this problem are: cID - PK for this table dpm_sub - FK for the subsidiary company dpm_pco - FK for the parent company year -…
Tamar E. Granor
  • 3,817
  • 1
  • 21
  • 29
0
votes
2 answers

MySQL 8 Recursive CTE Create row for each item

I would like to create a data return from a non existing table: +-------+--------+------------+ | type | name | expiration | +-------+--------+------------+ | fruit | orange | 1999-12-31 | | fruit | banana | 1999-12-31 | | fruit | apple |…
Jimmix
  • 5,644
  • 6
  • 44
  • 71
0
votes
2 answers

MySQL 8 Recursive CTE Error Code: 1054. Unknown column

This MySQL 8 query: WITH RECURSIVE cte_count (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM cte_count WHERE n < 3 ) SELECT * FROM cte_count; Produces: +------+ | n | +------+ | 1 | | 2 | | 3…
Jimmix
  • 5,644
  • 6
  • 44
  • 71
0
votes
1 answer

Find all dependencies between customers with recursive query ORACLE 11G

I'm trying to write a recursive CTE to give me all of the other customers a "primary customer" depends on, but I don't know where to start. I have created a sample data set and table structure: create table T_CUSTOMER_RELATIONSHIP (CUST_ID…
MAlabone
  • 11
  • 2
0
votes
1 answer

How to insert a Calender Items into Oracle DB records?

I'm trying to insert a full year Calender into an ORACLE DB records MY Columns are ---------------------------------------------------------------- | [FULL_DATE] | [DAY] | [MONTH_NAME] | [MONTH_NUMBER] | [YEAR]…
Amr Ali
  • 109
  • 1
  • 4
0
votes
2 answers

Performance tuning on Recursive CTE

I have the following table with sample data: Table: tbl_nodes create table tbl_nodes ( nod1 varchar(50), nod2 varchar(50) ); Sample data: insert into tbl_nodes values('Node1','Node2'); insert into tbl_nodes values('Node2','Node4'); insert…
MAK
  • 6,824
  • 25
  • 74
  • 131
0
votes
2 answers

Building sub-tree with correlated aggregate

I apologize for the vague title. I couldn't think how best to summarize the problem. I have a hierarchical table (e.g., ID int, ParentID int) and need to generate a sub-tree for an ID. This is trivially done with a recursive CTE. The difficulty is…
Daniel
  • 47,404
  • 11
  • 101
  • 179
0
votes
2 answers

PHP Recursive Directory Handle Problem

getSettings() seems to only read and output 1 settings.php file in the directory. How do I get it to read and output all the settings.php file contents?
inulled
  • 1
0
votes
2 answers

Select non existing Numbers from Table each ID

I‘m new in learning TSQL and I‘m struggling getting the numbers that doesn‘t exist in my table each ID. Example: CustomerID Group 1 1 3 1 6 1 4 2 7 2 I wanna get the ID which does not exist and…
Leo96
  • 13
  • 5
0
votes
1 answer

Required help/advise on Recursive Queries Using CTE

I'm trying to get repeated employees (Managers - director - CEO to have full hierarchy) with DepartmentCode similar to Lvl column from Recursive query result. Found couple of existing Q&A but those are not seem to fit with my case. The aim is to…
Shekar Kola
  • 1,287
  • 9
  • 15
0
votes
1 answer

Recursive query for postgresSQL parent/child

Asking for a little help on a recursive query syntax, and of course result. As you will see I have a table with category parent and child ( a parent can have infinite children). Querying the category dictionary (linked to a real category) And I want…
Ggs
  • 181
  • 15
0
votes
1 answer

T-SQL Query to Pull a Parent's Tags and all Children's Tags

Backgroud I have a DB Schema in SQL Server that looks like the following: http://sqlfiddle.com/#!18/dc3cf/3 | id | tag | child_id | |---- |----- |---------- | | 1 | A | | | 1 | | 4 | | 2 | C | …
Nxtq
  • 7
  • 2
0
votes
1 answer

Navmenu with Mysql in PHP as Recursive Data - Using 3 different table

I have made a three level categorization in my web page. Each articles goes through as SECTION->CATEGORY->SUBCATEGORY. section table has section_id and sectionname fields. category table has category_id, section_id, category name. subcategory table…
user723867
0
votes
1 answer

Run SELECT query recursively using DISTINCT rows from column

I'm running a query to count the number of occurrences of each value on each column. I've managed to accomplish this by running a for loop outside from SQL, but it is quite slow. The source table looks like this: Column 0 Column 1 Column 2 Column…
David
  • 1
  • 2
1 2 3
…
99
100