Questions tagged [hierarchical-query]
146 questions
2
votes
3 answers
SQL Server CTE to find path from one ID to another ID
I have a table in a SQL Server 2008 R2 database that defines transitions between different states.
Example relevant table columns:
TransitionID | SourceStateID | DestinationStateID |…
McFixit
- 437
- 3
- 15
2
votes
2 answers
Getting the very top level parts in a tree using Connect by
I have the following table:
CREATE TABLE Z_BOM2 (A VARCHAR2(4 CHAR), B VARCHAR2(4 CHAR));
Insert into Z_BOM2 (A, B) Values ('A', 'B');
Insert into Z_BOM2 (A, B) Values ('A', 'C');
Insert into Z_BOM2 (A, B) Values ('C', 'D');
Insert into Z_BOM2 (A,…
Darko
- 203
- 2
- 10
2
votes
3 answers
Hierarchical SQL Queries: Best SQL query to obtain the whole branch of a tree from a [nodeid, parentid] pairs table given the end node id
Is there any way to send a recursive query in SQL?
Given the end node id, I need all the rows up to the root node (which has parentid = NULL) ordered by level. E.g. if I have something like:
nodeid | parentid
a | NULL
b | a
c …
NotGaeL
- 8,344
- 5
- 40
- 70
2
votes
2 answers
Connect By in HSQL DB
I was writing test cases for query that uses connect by hierarchical clause.
It seems that there is no support for this clause in HSQL Db.
Are there any alternatives for testing the query or writing a different query that does the same thing.
The…
Harshit Agrawal
- 903
- 1
- 7
- 11
2
votes
2 answers
Oracle query to fetch data in the form of a Tree
I have a table with some data as follows
Id | Title | Category
-- ----- --------
1 Beans Food
2 Pizza Food
3 SQL Book
4 Avatar Movie
5 Oracle Book
I would like to organize this kind of table data in the…
Madhu Dollu
- 434
- 3
- 6
- 20
2
votes
1 answer
Oracle Hierarchical Query spanning across multiple tables
In the current project where I am working, I have come across a peculiar situation. Please consider the following table structure:
AGY_AGENCY (
AGENCY_ID NUMBER(9) not null,
AGENCY_CD VARCHAR2(30) not null,
AGC_LEG_CD …
Shovan
- 21
- 1
- 2
2
votes
1 answer
duplicating entries in listagg function
i have a table in which two fields are id, controlflag.It looks like
Id CntrlFlag
121 SSSSSRNNNSSRSSNNR
122 SSSNNRRSSNNRSSSSS
123 RRSSSNNSSSSSSSSSSSSSSS
I have to get output in the following form( the occurences of R)
Id …
Rajeena Safeer
- 91
- 1
- 1
- 4
2
votes
1 answer
What is the standard SQL query equivalent to Oracle's 'start with...connect by', but not DBMS specific
I would like to know if there is a generic SQL equivalent to Oracle's hierarchical syntax start with...connect by. I need something which can be used on any database. Here is the sort of query I mean (using Oracle's example EMP table):
SELECT…
Zeeshan Khan
- 23
- 4
2
votes
1 answer
How to create a hierarchical query as a materilized view with refresh on commit in oracle
Could anyone please tell me if there is a possibility to create a hierarchical query in oracle 10g as a materialized view with REFRESH ON COMMIT?
I tried using CONNECT_BY but this doesn't work with REFRESH ON COMMIT.
Are there any other…
Christian Hager
- 468
- 6
- 22
1
vote
3 answers
Oracle Hierarchical Query at depth level
I have a requirement to build a table from a hierarchical table. Table structure as below:
emp_hier table:
emp_id
supervisorId
100
null
1
100
2
1
3
2
New table:
I have to write a select query on the emp_heir table and the selected…
Vicki
- 43
- 7
1
vote
1 answer
increase performance of parent child query
i have a table with 4 million of record that contain parent_id and child_id and person table with flag field in oracle
i need plsql code with for loop and one person_id for input that get all relation of that person
the input id will be one of the…
Omid Ebrahimi
- 25
- 4
1
vote
1 answer
Oracle sql - Generate full hierarchical chain from link table
Supposing you have the following tables and data
create table articles (article_id number, name varchar2(30));
create table tags (tag_id number, parent_tag_id number, name varchar2(30));
create table associations (article_id number, tag_id…
RLOG
- 620
- 6
- 14
1
vote
1 answer
SQL query to obtain entire tree by root id
I have a table to store binary tree data in the following structure
Lets say for userId 27, currently I'm fetching a bunch of rows and building the tree in code.
Is there an efficient way to fetch the all the rows which belonging to the tree for…
Som
- 91
- 1
- 10
1
vote
3 answers
How to get root of hierarchial joined data in oracle 11g
My schema looks like this.
I've got hierarchial districts:
create table district(
id integer primary key,
name varchar2(32),
parent_id integer references district(id)
)
And houses within districts:
create table house(
id integer primary…
F0RR
- 1,590
- 4
- 16
- 30
1
vote
1 answer
hierarchical records using sql
I have a table like this
**id exType exId depType depId**
1 Ceo 1 Board Of Director 1
2 Board Of Director 1 Manager 4
3 Board Of…
David
- 17
- 3