Skip to main content

Questions tagged [hierarchy]

For issues relating to arranging items into ordered levels.

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

What is the best way in Postgres to inherit values from parent row recursively if not provided?

Is there a pattern in SQL where a child row inherits empty (null?) values from a parent? E.g., given the following 'chickens' table (sorry): id parent_id name flys noise weight egg_color ...
Sabrina Leggett's user avatar
0 votes
0 answers
66 views

Recursive CTE hiearchy snowflake -- join/ expand outward (columns) instead of rows?

I'm doing a dependency mapping explosion (like parts explosion). I did a typical recursive CTE with a union all. It looks like with CTE as ( select abc from myTable where start_point = X union all ...
user45867's user avatar
  • 1,683
0 votes
1 answer
76 views

SQL Pattern to get "root/ start" of uneven hierarchy dataset

I feel this is a common problem and I've seen it in some SQL challenges even but for the life of me, cannot think clearly about a solution. Say you have an uneven hierarchy. Elements that belong to ...
user45867's user avatar
  • 1,683
0 votes
0 answers
33 views

How can I duplicate adjacency lists without breaking parent-child relationships?

I would like to duplicate hierarchical structures, modelled as adjacency lists, in my database. The following tables and records allow a minimal reproduction: CREATE TABLE item_groups ( id serial ...
Check12's user avatar
  • 63
0 votes
0 answers
44 views

Finding tree contours using nested set model

Is there a query or algorithm that will enable me to find the left and right contours for general hierarchical trees and subtrees described using the nested sets model? I have described tree ...
Chris Walton's user avatar
0 votes
1 answer
331 views

What is the most straightforward way to create subtypes and supertypes at the same time?

I hope this isn't too basic a question, but I am hoping that the DB engineers here will help me out. Do relational DBs usually have a straightforward way of doing the following?: Create a supertable ...
Logos Masters's user avatar
0 votes
1 answer
24 views

Database for inheritance hierarchy queries

I'm looking for a way to store hierarchical data, in a way that will facilitate "inheritance" queries that will coalesce upwards in the hierarchy. Simple example: Level 1, property X = 123 -...
Ouananiche's user avatar
0 votes
1 answer
68 views

Normalisation with an Underlying Hierarchy

I'm designing a new database schema, and every time I do, I like to check my assumptions on normalisation. Something I haven't been able to find a definitive answer for here or by searching the web is ...
Vocoder's user avatar
  • 117
-1 votes
1 answer
24 views

Modeling combinations (options and dependencies)

How would you modelize the following data? I have a solution I wrote in a separate answer. Don't forget to read it. Context It's for a website. Speed is of the essence. Data You have objects that are ...
Some_user's user avatar
1 vote
0 answers
192 views

How to write query to link complex Parent-Child relationships

We have a table that has a DefectID field, and a PreviousDefectID field, in order to link defect records to each other as they change through time. I need to link these fields together by creating a ...
MM3000's user avatar
  • 11
-1 votes
1 answer
102 views

Creating a hierarchy from text

I'm trying to create a hierarchy from a text field I have in my table. Visually it is relatively easy to see who is parent and child. For example: (see a screenshot of the table results) 1M -----1M5 --...
Olivier's user avatar
1 vote
0 answers
225 views

MySQL: Hierarchical Organization of Tables

There are several SE Q&As regarding how to hierarchically organize tables in MySQL: How to use a naming convention for large databases? Asked 12 years, 11 months ago How can I organize a glut of ...
Jeron Baffom's user avatar
0 votes
1 answer
34 views

sql query for category heirarchy validations

I need to add validation on category creation. CASE 1: parentId should be valid if supplied CASE 2: name of sibling could not be duplicated I have this table: (categories) id | parentId | name ...
StormTrooper's user avatar
0 votes
2 answers
5k views

System/database design for comments/replies and upvotes at scale

Recently I started discovering a topic around "How to design db schema for storing structures similar to Instagram/Facebook/Reddit comments?". After extensive research, I was able to find a ...
IDobrodushniy's user avatar
0 votes
0 answers
114 views

Database design for a forum

I am trying to build a forum kind of like reddit. Discussions can have comments and people can comment on comments. My solution for such a hierarchical database is to use a closure table for my ...
PVit's user avatar
  • 1
3 votes
2 answers
1k views

CTE get all descendants with parents

Schema: CREATE TABLE item ( id int primary key, parent_id int, FOREIGN KEY(parent_id) REFERENCES item(id) ); INSERT INTO item (id, parent_id) VALUES (1, NULL), (2, 1), ...
mustafa.0x's user avatar
3 votes
0 answers
611 views

How to map child (leaf-level) nodes to specific parents with/without ltree for aggregations?

What would be the best way to aggregate across hierarchies? I am using Postgres and have the following 3 tables (folder, folder_hierarchies, and work) - folder Column Type Description folder_id (pk)...
GopherGopher's user avatar
1 vote
1 answer
180 views

Query hierarchical file permissions

I have a table representing some files with their paths and another table with file sharings by user and files. Below is Table files, simplified. Example: I have a directory A (path /A) containing ...
Olivier's user avatar
  • 13
2 votes
1 answer
54 views

Is there any harm in storing other rows' int PKs in a hierarchyID instead of an arbitrary number?

I'm looking at common uses of hierarchyID, and most examples use some sort of arbitrary numbering for the HID nodes: HID, Name /, World /1/, USA /1/1/, Texas /1/1/1/, Houston /1/1/2/, San Antonio /1/2/...
XOR cyst's user avatar
1 vote
1 answer
99 views

What facilities of SQLServer would help achieve a goal of tracking a hierarchy over time?

I've a requirement to create a system that tracks what is essentially a hierarchy of containers over a period of time, modelling a supply chain. Everything in the chain is a container, so the process ...
XOR cyst's user avatar
2 votes
0 answers
123 views

MS-SQL and HierarchyID

I have been using HierarchyID for the last 2 years in some of my tables for obvious purposes And I have kept a Column (int) for the last node number for example if I have these rows so the int column ...
Y.G.J's user avatar
  • 119
2 votes
0 answers
596 views

Postgres ltree: Get all accessable resources by groups and sub groups

Have following Data scheme: Roles: Each role has either a resource assigned or a resource_group. A user wich has a role on a group has also access to all resources that are within the group or within ...
Karpo72's user avatar
  • 21
0 votes
1 answer
169 views

MSSQL Merge 3 hierarchy tables

I want to merge 3 hierarchy tables into one table. The 3 tables: CREATE TABLE dbo.tbl1 ( key1 varchar(50) NULL, key2 varchar(50) NULL ) ON PRIMARY CREATE TABLE dbo.tbl2 ( key2 varchar(50) ...
17thelement's user avatar
1 vote
3 answers
693 views

Constraint to prevent immediate loop in hierarchy

I have a companies table: CREATE TABLE companies ( id bigserial, name varchar(255) NOT NULL PRIMARY KEY (id) ) I want companies to have parents and childs. A company might have many parent and ...
bruno's user avatar
  • 13
3 votes
1 answer
5k views

Recursively find the path to all leaves descending from a given parent in a tree

I'm trying to write a query to identify the path to each furthest descendant of a particular parent node in a table of tree structures like: 0 1 | | 2 3 | | 4 5 ...
Jason Dreyzehner's user avatar
2 votes
1 answer
118 views

Org chart CTE with exact index but plan shows expensive sort node

I've got a moderately large table (148 million rows) that's got pretty common org chart attributes to it. Names are changed, but it's the same idea. I was thinking of adding a hierarchyid column to ...
user1664043's user avatar
3 votes
1 answer
209 views

Ordering CONNECT BY siblings

I have an hierarchical query that uses CONNECT BY: select wonum, parent, classification, classstructureid, division, worktype, status, glaccount, fircode, ...
User1974's user avatar
  • 1,477
1 vote
1 answer
296 views

Relationship design for areas, managers and general manager

Given some specifications I need to design a database for an organization. One of the parts has to do with areas. In the organization there are different areas, each area has a boss, and there is also ...
luisepa1420's user avatar
0 votes
1 answer
333 views

How to improve poor performance with two joined CTE expressions on hierarchical data?

I have a table of hierarchical data where I want to select entries that, 1) only include matches that are descendants of a given parent, and 2) returns the full hierarchical path of the matched ...
Eliott's user avatar
  • 103
1 vote
0 answers
994 views

Recursively list dependencies / references of a stored procedure

I'm trying to import a subset of our production dump with impdp for our local development database running on Oracle XE. To find out what other objects I have to import for a particular stored ...
stuXnet's user avatar
  • 121
0 votes
1 answer
70 views

Aggregating nested set models with field constraints

I have a table built on the nested set model that basically records transactions of different natures (defined by the field idx): pk lft rgt idx value 1 1 30 2783 20402.710000 2 ...
manoelpqueiroz's user avatar
9 votes
1 answer
655 views

Find friends of friends (recursively) efficiently using Postgresql

Objective: Users submit their Contact Books, and then the application looks for connections between users, according to their Phone Number. Something like "6 Handshakes" idea (https://en....
Viktor Vsk's user avatar
1 vote
2 answers
2k views

Hierarchical data using closure table, how to ensure that a column is unique within the parent node?

I store hierarchical data in a Node-table, combined with a closure table (NodeClosure). Each node has a column "name" that should be unique among the sibling nodes (link to parent-Node + ...
Tommy Carlier's user avatar
2 votes
3 answers
1k views

Foreign key reference to column that is no primary key

I know, foreign keys should uniquely identify rows and therefore reference a primary key. However, consider my example: There are 4 pre-defined categories, and for some combinations of values for ...
David's user avatar
  • 121
0 votes
1 answer
884 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 ...
Jacob Stamm's user avatar
-1 votes
1 answer
1k 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; ...
Khalifa's user avatar
-1 votes
1 answer
305 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 ...
Przemyslaw Wojda's user avatar
1 vote
1 answer
102 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, ...
User1974's user avatar
  • 1,477
1 vote
1 answer
233 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 ...
WebbyLass's user avatar
0 votes
1 answer
20 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 ...
Alex Kinman's user avatar
0 votes
0 answers
64 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, ...
Fabio Branco da Silva's user avatar
0 votes
1 answer
2k 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 ...
Arash Ammarlooi's user avatar
2 votes
1 answer
332 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 ...
Cristiano Lagame's user avatar
2 votes
1 answer
3k 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 ...
Luke's user avatar
  • 143
1 vote
0 answers
391 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. ...
jrjones1956's user avatar
3 votes
2 answers
5k 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 ...
Lenny32's user avatar
  • 133
4 votes
3 answers
103 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) )...
Jay's user avatar
  • 145
0 votes
1 answer
500 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 ...
tictoc's user avatar
  • 31
3 votes
2 answers
4k 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 ...
user149251's user avatar
4 votes
1 answer
2k 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 ...
Arthur Putnam's user avatar