Questions tagged [hierarchy]

For issues relating to arranging items into ordered levels.

Filter by
Sorted by
Tagged with
0
votes
1answer
284 views

SQL relationship for nested groups

I'm working on a creating a database schema for a legacy app that currently saves its data to XML files. I'm having trouble representing this relationship: Task Group 1 contains |-- Simple Task 1 |--...
2
votes
1answer
963 views

Parent Child Relation in same Table . Select Query to get only parent records and Records not comes under parent child relation

I have a table with Parent-Child Relation in the same Table. Some Sample Data id │ name │ parent │ is_active ════╪══════════════════════╪════════╪═══════════ 1 │ Company A ...
0
votes
1answer
37 views

Pattern for copying hierarchical data from one table to another?

Imagine a table with three columns: an ID primary key, a nullable ParentID referencing the ID column on that same table, and a TreeID to indicating the records that make up a particular tree. A basic ...
0
votes
1answer
159 views

Procedure for tree view

I trying to fetch data form database which is have 3 column unique_id, parent_id & node_name, but this query (Given in below) is return only two rows. Condition: every parent has two child. DROP ...
2
votes
1answer
491 views

SSAS Cube for tracking changes in parent child relationship over time

I would like to build an SSAS cube which tracks how objects in a graph who's edges represent a "belongs to" relationship change over time (daily). There are two components to the change: which object ...
0
votes
1answer
831 views

While Loop - Parent/Child Tree

I'm trying to recursively loop through and return all child_id's that have the root element of 9. The structure: ->9 ->->8 ->->->17 ->->22 ->->->11 Parent Child ...
-1
votes
1answer
50 views

SQL Variables inside SELECT query in a function

MariaDB 10.3.14 I have a function that generates the root id in a hierarchy: The function is this: CREATE FUNCTION `getRootId`(GivenID INT) RETURNS int(11) DETERMINISTIC BEGIN DECLARE ch INT; ...
0
votes
1answer
150 views

Bypass truncated data type error in `CREATE TABLE AS`

I am attempting to recreate the code from this excellent tutorial in MariaDB 10.2 to try and build nested sets from an existing adjacency list. (The goal being to ingest changes to the adjacency list ...
-1
votes
1answer
26 views

MS SQL 2017: hierarchy tree - get quantity of last level components

There's a table of items with their components. Components depth vary i.e. some items may have one level of components other may have tens nested components. I need to get quantinty of last level ...
0
votes
1answer
510 views

How make recursive json on mysql

my table looks like CREATE TABLE `test_comments` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `parent_id` int(10) unsigned DEFAULT NULL, `test_id` int(10) unsigned NOT NULL, `user_id` int(...
1
vote
1answer
604 views

Social network comments and posts model in PostgreSQL 9.6

I'm trying to figure out a data structure for migrating from MongoDB to PostgreSQL. Within my posts table in Postgres, I have a JSONB[] array holding comments for the post. The thing is that each ...
2
votes
3answers
8k views

How to build a table for a private messaging system that supports replies?

I'm writing a private messaging web application in PHP, the application allows replies such that when you view a message, you also get to see to what was that a reply to, and to what was that a reply ...
1
vote
1answer
30 views

Generate hierarchical path

I have a classstructure table: create table classstructure (classstructureid number(8,0), classificationid varchar2(25), parent number(8,0)); insert into classstructure(classstructureid, ...
3
votes
1answer
555 views

How to do hierarchical queries without CTE tables or recusive queries in Snowflake? (parent, child relationships)

I have several tables in Postgres that we are migrating to Snowflake. Some of these table are hierarchical (a foreign key point to the same table), normally to query this data I use CTE tables and/or ...
1
vote
1answer
96 views

Database Design - hierarchical structure

We are trying to redesign a database that currently holds thousands of parameters for different datatypes and currently has a flat data structure. We generate XML files from the database, which our ...
1
vote
1answer
631 views

How to get last level of a hierarchical list, group by some field

Given the schema: create table containers ( id int primary key, name text, product_id int, lot int, qty int, parent_id int); create table orders_items ( id int, ...
0
votes
1answer
17 views

Is there such a thing as an OLAP cube that can change the number of levels in a hierarchy without having to be rebuilt?

The current analytics solution that my team works with cannot handle changes in the structure of one of its hierarchies. If a new level or branch is added, then the entire domain must be first backed ...
0
votes
0answers
38 views

How to retrieve all children of a parent record?

I have one MySQL table with this columns: id father son street city I want to obtain all descendants of a father recorded, how I can do this? I tried this: WITH RECURSIVE filhos AS ( select login, ...
1
vote
1answer
427 views

Oracle hierarchical query: aggregate over each node's descendants, give results in tree pre-order

Given hierarchical data, for each item I need to get the sum of a column over the sub-tree rooted at the item, and I need the results in pre-order. Example: for the data employee_id | manager_id | ...
0
votes
2answers
137 views

Hierarcy Query Mysql [duplicate]

I have a table like this... ========================================================================== UID || PARENT ID || SPONSOR ID ===============================================...
0
votes
0answers
379 views

Sql subquery for nested parent child data

I have two tables , on parent table (categories ) and child table (features).. I have this query select c.* , (select GROUP_CONCAT(f.featuresId , f.featuresTitle ) from ...
4
votes
2answers
974 views

Hierarchical queries without CTEs

I'm new to MySQL. The GetFamilyTree(id) function that Rolando suggested in the Find highest level of a hierarchical field: with vs without CTEs question was great but when the id is greater than 999 ...
21
votes
5answers
8k views

Anybody using HierarchyId in production? Is it reliable?

Is anybody using HierarchyId in real production with tables of reasonable size, more than a few thousand rows? Is it reliable/performant? So far I have not found anyone not affiliated with the vendor ...
2
votes
1answer
45 views

How to select id of father from the child id recursively

I have in the field cod_categorizacao_pai the ID of the father, and I need to mount a select that just enter an ID and it will list the paternal IDs of this ID until it reaches the parent of all. How ...
2
votes
1answer
564 views

How to query all entities associated with all children (recursive) of a node in SQL?

This question is about SQL in general. Answering specifically for MySQL would be helpful but not necessary. Ok, I’m having trouble putting this into words… so bear with me. Say I have a tree of ...
0
votes
1answer
150 views

Join mysql tables to find siblings and get their notifications

i have two mysql tabes one for company and one for notifications. Company +-----------------------+ | ID |parentID|vaue | |1 |0 |22020 | |2 |1 |23423 | |3 |2 |22222 ...
1
vote
0answers
169 views

Worked example of closure tables with from and thru dates

I work for a law enforcement agency, and have been asked to create a way of storing the chain of command in such a way that its structure can be viewed at any point in time, not only as it is now. ...
3
votes
3answers
1k views

SQL Server CTE Bottom to Top Recursive with Where clause

I have an Employee Table with an EmployeeId, ManagerId and a Name field. The goal is to make a recursive With fetching all the rows from an employee to the top manager (ManagerId is null). I found ...
-2
votes
2answers
102 views

What's the best way to represent an event from a system in a chat?

Conversations -------------- id Messages -------------- id conversation_id user_id reply_to_id content created_at I was looking at this question and I had a question: How to build a table for a ...
4
votes
3answers
66 views

How can I pass on a value in the hierarchy based on the value of the parent using an hierarchial query?

I have a table with categories and another table with a discount for categories per user: create table category ( id NUMBER NOT NULL, parent_id NUMBER, name nvarchar2(255), PRIMARY KEY( id) )...
7
votes
1answer
4k views

Stored Procedure to update an adjacency Model to Nested Sets Model

If you don't already know, these two models are the most common ways to store a tree in a relational DB. Adjacency Model: +-------------+----------------------+--------+ | category_id | name ...
3
votes
0answers
51 views

Hierarchical database schema design - X500 data model [closed]

I have to make a centralized database for appliance configurations, and SQL is a no-go because was evaluated as too expensive in terms of resources for read-mostly load. We planned to use ...
2
votes
2answers
3k views

How to flatten and pivot a normalised hierarchy table

I have an OrgChart table that looks something like this: PositionCode PositionName ParentCode Level AA0001 CEO 0 AA0002 CFO AA0001 1 AA0003 ...
-1
votes
2answers
16k views

Return hierarchical data from a self-referencing table (parents, children)

I have the following SQL Server 2008 table and data: CREATE TABLE dbo.MyTable (Sno int, refid int); INSERT INTO dbo.MyTable VALUES (17, 8), (16, 8), (15, 7), (14, 7), (13, 6), (12, 6), ...
3
votes
4answers
3k views

Faster alternative to scalar UDF with recursion to walk tree hierarchy?

I have a scalar function that traverses a simple parent-child hierarchy of customers to find the ancestor that's in charge of billing. Here's a simplified version of the schema. CREATE TABLE Customer ...
0
votes
1answer
147 views

Advantages of a hierarchical schema for private chat

This post claims that All modern chatting interfaces, without exception, implement a hierarchical and a not-chronological schema for chat. It also links another post which gives an example ...
0
votes
1answer
81 views

Hierarchial, tree-like data model, which DB type? [closed]

I have the need for a database, I know what to store, however the question is what database to use and why. I have used relation SQL databases a lot in the past (up to a few years ago) and I know I ...
1
vote
0answers
205 views

How to get the hierarchy of particular employee using JPA spring boot?

I am trying to find a way to get the employee hierarchy in an organization using JPA and Spring boot. The problem is similar to the following link Similar Data Model If A reports to B and B reports ...
2
votes
0answers
50 views

Design for a child to have a single parent `A` OR `B` with FK cascade constraint?

I'm assuming this is not possible with PK and FK... Let's say I have tables A, B, and C. A and B are "parents", and B and C are "children" A is the root B can be the child of either A or B C ...
1
vote
0answers
63 views

Scalable way to manage aggregated statistics over time in hierarchy?

I'm using a closure table (Postgres through Django) to roll up stats through a hierarchical tree. When a node that is 10 layers deep has a value that increases, each parent's sum total of that value ...
1
vote
0answers
52 views

Nested set model in higher dimensions

The nested set structure is well-known and involves using left and right values to denote the relative heirarchy of nodes on one axis. Is there a generalization of this structure to higher dimensions?...
2
votes
1answer
9k views

Get top most parent by nth child id?

Now there is a question we commonly use this technique to maintain the parent child relation i.e we store all the entities in one tables with a parent_id column and all top most parents have 0 in the ...
7
votes
3answers
12k views

Parent/Child Relationship Table Design - What's The Best Practice?

I have a single table for storing 'Tasks'. A task can be a parent and/or a child. I use the 'ParentID' as the FK referencing the PK on the same table. It is NULLABLE, so if it is NULL it does not have ...
2
votes
1answer
120 views

Correct way to join row in parent table to row in one of several children tables

I have a parent table product, and several children tables like phone, tablet, smartwatch etc. The parent and children have 1-1 relationship. This means that for every row in parent table, there will ...
1
vote
1answer
36 views

How to add foreign key in a table where column value can come from two different tables?

I have three tables create table main_comments ( id serial primary key, comment text not null ); create table sub_comments ( id serial primary key, main_id integer not null, comment ...
3
votes
2answers
389 views

Get all descendants from 3 hierarchical tables

I have the following tables within a database: +-------------+ +-------------+-------------+ +---------------------------+ | project1 | | project2 + | project3 ...
3
votes
2answers
59 views

Nullable, dependent attributes

I'm designing a database for students. Computer science students here in Switzerland can decide between different specializations, e.g. application development, system engineering etc. There are ...
5
votes
1answer
2k views

Condition in WHERE vs condition in CONNECT BY

Can anyone explain to me the difference between the following two queries? Even though they seem the same the results are different. select concept.concept_id, concept.PARENT_ID from ...
3
votes
1answer
83 views

How to store hierarchical dimension for timeseries data

I have a requirement to store data about a load of Searches. Each time someone searches we want to record it for analyzing later on. As part of the search the user can select one or more Categories ...
1
vote
1answer
70 views

Select top-most folder in a hierarchy by given criteria in MySQL

I'm using MySQL (5.7.20) to store a folder hierarchy. Each folder is stored together with all direct and indirect parents including the distance. As an example, the following folders A +- B + C ...