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

Split space separated values and map them to original ID in SQLite?

I have a table called personal_websessions that contains data in the following format: id_no | website_link 1 | google.com msn.com gmail.com 2 | stackoverflow.com reddit.com 3 | msn.com You can create this table using the…
Beans On Toast
  • 903
  • 9
  • 25
5
votes
5 answers

SQL - Insert rows based on count

I'm looking to insert a number of repeating rows into a table, based upon the value set by another - ideas & suggestions would be greatly appreciated. tblType Type Qty Apple 2 Banana 1 Mango …
BigIWT
  • 243
  • 1
  • 5
  • 14
5
votes
2 answers

SQL Server : Using recursive CTE to resolve group membership

I have one table (users_groups) : +-----------+------------+---------+ | groupGUID | memberGUID | isGroup | +-----------+------------+---------+ | 32AB160C | 5B277276 | 0 | | 32AB160C | 0A023D1D | 0 | | 5C952B2E | 32AB160C | …
Trevor65
  • 328
  • 1
  • 8
5
votes
1 answer

Deleting with self-referential foreign key in Postgres

With a table with a self-referential foreign key: CREATE TABLE tree ( id INTEGER, parent_id INTEGER, PRIMARY KEY (id) ); ALTER TABLE tree ADD CONSTRAINT fk_tree FOREIGN KEY (parent_id) REFERENCES tree(id); INSERT INTO…
ANisus
  • 74,460
  • 29
  • 162
  • 158
5
votes
4 answers

Oracle: get countries separated by N borders

I would like to get all countries separated by N (1,2,3,4 ...) borders from a specified country. N should also be specified. For example I have the table "borders" and "country": border | neighbor ----------------- FR | DE FR | IT …
Hayk
  • 71
  • 6
5
votes
1 answer

WITH RECURSIVE as second part CTE in query. Postgres

How I can write a query like that: with t1 as ( select id from table1 ), RECURSIVE t2( select * from t2 union ... ) Currently it's not allowed?
Sergey Ivanov
  • 51
  • 4
5
votes
4 answers

Selecting all descendant rows from an Oracle table representing a tree structure

I have a table MYTYPE in Oracle 10g representing a tree structure, which is something like this: ID | PARENTID | DETAIL I would like to select all rows in MYTYPE which are descendants of a particular ID, such that I can create queries elsewhere…
ckpwong
  • 2,129
  • 1
  • 17
  • 17
5
votes
1 answer

Applying CTE for recursive queries

I am trying to apply CTE and recursive queries. The database is MariaDB 10.2 or greater. Business rules are as follows: An account can either be a holding or a portfolio. A holding consists of a given amount of money. Holdings can be active and…
user1032531
  • 24,767
  • 68
  • 217
  • 387
5
votes
2 answers

sql recursive function - to find managers

Lets say I have the following table User_ID Manager_ID --------------------- Linda Jacob Mark Linda Kevin Linda Steve Mark John Kevin Basically the requirement is to pull all the managers under the…
Jacob
  • 73
  • 2
  • 5
5
votes
1 answer

Postgres count with self referential join condition

Given the following structure CREATE TABLE products ( id integer NOT NULL, subcategory_id integer, stack_id integer, ) CREATE TABLE subcategories ( id integer NOT NULL, name character varying(255) ) Where products.stack_id is a…
djohnson
  • 88
  • 5
5
votes
3 answers

SQL grouping interescting/overlapping rows

I have the following table in Postgres that has overlapping data in the two columns a_sno and b_sno. create table data ( a_sno integer not null, b_sno integer not null, PRIMARY KEY (a_sno,b_sno) ); insert into data (a_sno,b_sno) values ( 4,…
bogeyman
  • 169
  • 1
  • 7
5
votes
1 answer

Aggregating connected sets of nodes / edges

I have a connected set of edges with unique nodes. They are connected using a parent node. Consider the following example code and illustration: CREATE TABLE network ( node integer PRIMARY KEY, parent integer REFERENCES network(node), length…
Mike T
  • 41,085
  • 18
  • 152
  • 203
5
votes
2 answers

get ALL last level children (leafs) from a node (hierarhical queries Oracle 11G)

I am trying and searching the way to get ALL last level children (leafs) from a node, in a hierchical query in Oracle 11g database. I have 2 tables: "Nodes" (A list of all nodes with their respective value), and "Relation" which specify…
AlexAcc
  • 601
  • 2
  • 10
  • 28
5
votes
2 answers

How can I paginate with Spring Security, Hibernate and row level ACL

I'm reading about Spring Security and wonder whether it's possible to use Spring ACL together with hibernate and pagination. The resulting SQL is surely scary but possible to be auto-generated. It's even possible to use hierarchical ACL if the…
Thomas Koch
  • 2,833
  • 2
  • 28
  • 36
5
votes
4 answers

SQL Recursive CTE: preventing a recursive loop by multiple recursive references

Question I have a recursive CTE query, but it fails when a loop is created. I already fixed simple loops (e.g. 1 -> 2 -> 1), but cannot fix more complex loops (e.g. 1 -> 2 -> 3 -> 2). Query Detail The test table has two columns: Base and Parent. I…
Peet Brits
  • 2,911
  • 1
  • 31
  • 47
1 2 3
…
99 100