Questions tagged [recursive]
Using recursion to solve database problems.
262 questions
2
votes
1
answer
3k
views
Intentionally recursive instead of insert trigger does not want to nest
I have been struggling to get an instead of trigger to recursively call itself.
The idea is that when a row gets inserted into my table the trigger is placed on, based upon certain conditions ...
- 133
7
votes
2
answers
6k
views
Query to find circular references
I have two tables
ID Task
1 1
2 2
3 3
4 4
Col1 depend
2 3
2 4
3 1
4 2
ID and Col1 are related through FK constraint. I want to find out all circular references. Here ID ...
- 111
19
votes
1
answer
14k
views
How do I sort the results of a recursive query in an expanded tree-like fashion?
Let's assume you have a nodes tables like this:
CREATE TABLE nodes (
node serial PRIMARY KEY,
parent integer NULL REFERENCES nodes(node),
ts timestamp NOT NULL DEFAULT now()
);
It ...
- 525
4
votes
1
answer
5k
views
Making a recursive CTE like query, but for many records
I have a table with a hierarchy ( with a recursive parentID column), for one record, I know how to get the last parent record, with the following code :
declare @Id integer
;WITH CTE AS
(
SELECT ...
- 43
1
vote
1
answer
2k
views
Self-join unique pairs
I have a table (players) containing a list of players, I would like to pair up these players in a unique way, so that every player's opponent is unique every round (ie. every time the SELECT query is ...
- 588
2
votes
2
answers
703
views
SQL Server - Recursive "find all FK connections for ID through the entire DB" query
I'm dealing with a DB with hundreds of tables, with interconnecting FK references producing potentially thousands of different permutations for how for example table A and table K can join together. ...
- 1,813
2
votes
1
answer
14k
views
Recursive Query in MySQL using stored proceedure and CURSOR
I'm expanding our user/group system to allow for dynamic groups which are made up of other groups members. I have three tables users, groups, and relationships.
For simplicity sake, lets say users ...
- 506
0
votes
1
answer
1k
views
Flatten recursive hierarchy
I have the following recursive query:
SELECT SYS_CONNECT_BY_PATH(object_name, '\') AS path
FROM user_group_nodes
START WITH parent_id = '2892107544AA2278166C1AA8D123E761'
CONNECT BY parent_id = PRIOR ...
- 131
2
votes
1
answer
2k
views
Tree structure hierarchical query sorting
I have the following structure of table.
CREATE TABLE table1
(
serial_num integer NOT NULL,
parent_num integer,
parent_key text,
key_ary_num integer[],
level integer,
rank integer,
...
- 33
2
votes
1
answer
158
views
How do I query a table with many trees of data, for one tree?
I have a the NAICS schema loaded and all is working well. However I'm confused as how I'd query it in Postgrseql?
The table looks something like this,
=========NAICS_2012=========
naics | ...
- 64.6k
8
votes
1
answer
6k
views
Recursive CTE performance
Need help with recursive CTE performance. Below CTE is running very slow as it is trying to pull heirarchical
data recusively. Table is big with every root id having upto 3 recursive itemid. There ...
- 101
33
votes
2
answers
12k
views
Using EXCEPT in a recursive common table expression
Why does the following query return infinite rows? I would have expected the EXCEPT clause to terminate the recursion..
with cte as (
select *
from (
values(1),(2),(3),(4),(5)
) ...
- 2,179